Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care.
Let’s look at the following example
SQL> create table T ( 2 x varchar2(20) , y varchar2(100)); Table created. SQL> insert into T 2 select 'x' , rpad('z',100) from all_objects; 94117 rows created. SQL> insert into T 2 select 'X' , rpad('z',100) from all_objects; 94117 rows created. SQL> exec (more...)
For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes.
SQL> create table blah ( x varchar2(30)); Table created. SQL> create index blah_ix on blah ( upper(x)); Index created. SQL> select column_name from user_ind_columns 2 where index_name = 'BLAH_IX' 3 / COLUMN_NAME ------------------------------ SYS_NC00002$
Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.
SQL> select column_expression (more...)
Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist
SQL> drop table PAR cascade constraints purge; Table dropped. SQL> create table PAR ( p int primary key, data int); Table created. SQL> insert into PAR 2 select rownum, rownum 3 from dual connect by level <= 7; 7 rows created. SQL> SQL> drop table CHD purge; Table dropped. SQL> create table CHD ( c int (more...)
Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc).
However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the documentation provides a more useful description:
If the session STATUS is currently ACTIVE, then the value represents the elapsed (more...)
With Collaborate 2016 under way, there seems no better time to reflect on why PL/SQL is the natural choice for anyone who loves to code, and loves their data
As always happens from time to time, we had the following request on AskTom today:
Could you list down 2 reasons why sql server is better than oracle?
Then 2 counter reasons as to why why oracle is better than sql server
And I thought I’d reproduce my response here
I’ll answer it slightly differently…
Q: When is SQL Server better then Oracle ?
A: When you have good, intelligent, knowledgeable SQL Server people (more...)
When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10″ within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run. To help you understand, a nice little utility is to use EVALUATE_CALENDAR_STRING”
SQL> set serveroutput on size 999999 SQL> SQL> declare 2 l_start_date TIMESTAMP; 3 l_next_date TIMESTAMP; 4 l_return_date TIMESTAMP; 5 begin 6 l_start_date := trunc(SYSTIMESTAMP); 7 l_return_date := l_start_date; 8 (more...)
AskTom has been answering questions from the user community for nearly 16 years.
But what if that service could have an even more personal touch ? We are pleased announce the largest advance in AskTom service since it started way back in 2000.
See all the details in the video below for the new service we are launching on April 1st.