Search My Oracle Blog

Custom Search

31 January, 2010

Common Errors Series

I will be publishing some "Common Errors" by DBAs and Developers. These errors occur in the course of performance tuning or database or instance configuration or in understanding oracle concepts.

The first of these is about using a count(*) or a count(1) as a wrapper around a user's query when attempting performance tuning.

The second of these is about specifying NOLOGGING as a Hint when executing DML.

The third of these is about NOLOGGING and Indexes.

The fourth is about ARRAYSIZE.


The sixth is about Not collecting metrics.

The seventh is about the behaviour "We killed the job because it was hung".
.
.
.

13 January, 2010

DDL on Empty Partitions -- are Global Indexes made UNUSABLE ?

Although it is documented and well known that Partition maintenance operations (DDLs like DROP, TRUNCATE, SPLIT, MERGE) make Global Indexes UNUSABLE (unless the UPDATE GLOBAL INDEXES clause is added to the DDL statement), this isn't necessarily true if the Partition is *empty*.

Here I present a Test Case with TRUNCATE and DROP statements issued against empty and not-empty Partitions. Such statements against an empty Partition do NOT make the Global Index UNUSABLE. It is as if Oracle checks to see if there are rows present in the Partition when doing a TRUNCATE or DROP and, if there are no rows, does not set the Global Index UNUSABLE.
(We accept that from normal usage against Tables, Oracle doesn't bother to check if the Table contains any rows or not when issuing a TRUNCATE or DROP. But here we find that it does matter when the segment is a Partition of a Table).



SQL>
SQL> REM ############ Setup the Table and Indexes ####
SQL> REM ##############################################
SQL> drop table loaded_part_table purge;

Table dropped.

SQL>
SQL> create table loaded_part_table (owner varchar2(30) not null, seg_year number not null,
2 segment_name varchar2(81), tbs_name varchar2(30))
3 partition by range (owner, seg_year)
4 (
5 partition l_p_dbsnmp_2007 values less than ('DBSNMP',2008),
6 partition l_p_dbsnmp_2008 values less than ('DBSNMP',2009),
7 partition l_p_dbsnmp_2009 values less than ('DBSNMP',2010),
8 partition l_p_hemant_2007 values less than ('HEMANT',2008),
9 partition l_p_hemant_2008 values less than ('HEMANT',2009),
10 partition l_p_hemant_2009 values less than ('HEMANT',2010),
11 partition l_p_noone_2007 values less than ('NOONE',2008),
12 partition l_p_noone_2008 values less than ('NOONE',2009),
13 partition l_p_noone_2009 values less than ('NOONE',2010),
14 partition l_p_sys_2007 values less than ('SYS',2008),
15 partition l_p_sys_2008 values less than ('SYS',2009),
16 partition l_p_sys_2009 values less than ('SYS',2010),
17 partition l_p_max values less than (MAXVALUE, MAXVALUE)
18 )
19 /

Table created.

SQL>
SQL> create index l_p_t_gbl_ndx on loaded_part_table (owner, seg_year);

Index created.

SQL> create index l_p_t_local_ndx on loaded_part_table (owner, seg_year, tbs_name) LOCAL;

Index created.

SQL>
SQL>
SQL> REM ############ Setup the Data #################
SQL> REM ##############################################
SQL> -- Rem Now Insert data into selected partitions of LOADED_PART_TABLE
SQL>
SQL> insert into loaded_part_table
2 select owner,2008,segment_name,tablespace_name
3 from dba_segments where owner in ('DBSNMP','HEMANT','SYS')
4 and segment_type = 'TABLE'
5 /

630 rows created.

SQL> insert into loaded_part_table
2 select owner,2009,segment_name,tablespace_name
3 from dba_segments where owner in ('DBSNMP','HEMANT','SYS')
4 and segment_type = 'INDEX'
5 /

706 rows created.

SQL> insert into loaded_part_table
2 select 'XYZ',2011,'XYZ_SEG','XYZ_TBS'
3 from dual
4 /

1 row created.

SQL> commit;

Commit complete.

SQL> -- the last insert was into the MAX partition !
SQL> select count(*) from loaded_part_table partition (L_P_MAX);

COUNT(*)
----------
1

SQL>
SQL>
SQL>
SQL> -- REM ###############################################################################
SQL> -- current status of indexes
SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /

IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX VALID
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A

15 rows selected.

SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run a DDL -- TRUNCATE an empty partition
SQL> select count(*) from loaded_part_table partition (l_p_noone_2008);

COUNT(*)
----------
0

SQL> alter table loaded_part_table truncate partition l_p_noone_2008;

Table truncated.

SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes
3 where table_name = 'LOADED_PART_TABLE'
4 union
5 select 'Local' ind_type, index_name, partition_name, status
6 from user_ind_partitions
7 where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
8 order by 1,2,3
9 /

IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX VALID
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A

15 rows selected.

SQL> REM REM REM !! The GLOBAL Index is still VALID ! We had TRUNCATEed an empty partition !!
SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run another DDL -- TRUNCATE a used partition
SQL> select count(*) from loaded_part_table partition (l_p_dbsnmp_2008);

COUNT(*)
----------
17

SQL> alter table loaded_part_table truncate partition l_p_dbsnmp_2008;

Table truncated.

SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /

IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX UNUSABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A

15 rows selected.

SQL> REM REM REM !! The GLOBAL INDEX is now UNUSABLE !
SQL>
SQL> -- rem rebuild the GLOBAL INDEX
SQL> alter index l_p_t_gbl_ndx rebuild;

Index altered.

SQL> select status from user_indexes where index_name = 'L_P_T_GBL_NDX';

STATUS
--------
VALID

SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run another DDL -- DROP a partition that is empty
SQL> select count(*) from loaded_part_table partition (l_p_hemant_2007);

COUNT(*)
----------
0

SQL> alter table loaded_part_table drop partition l_p_hemant_2007;

Table altered.

SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /

IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX VALID
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A

14 rows selected.

SQL> REM REM REM !! The GLOBAL INDEX is still VALID ! We had DROPped an empty partition !
SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run another DDL -- DROP a partition that has rows
SQL> select count(*) from loaded_part_table partition (l_p_hemant_2009);

COUNT(*)
----------
12

SQL> alter table loaded_part_table drop partition l_p_hemant_2009;

Table altered.

SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /

IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX UNUSABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A

13 rows selected.

SQL> REM REM REM !! The GLOBAL INDEX is now UNUSABLE !
SQL>
SQL> -- rem rebuild the GLOBAL INDEX
SQL> alter index l_p_t_gbl_ndx rebuild;

Index altered.

SQL> select status from user_indexes where index_name = 'L_P_T_GBL_NDX';

STATUS
--------
VALID

SQL>



Notice the status of the Global Index if the Partition that was TRUNCATEd or DROPped was an empty partition -- there is no change !

(tests on 10.2.0.2 and 10.2.0.4)
.
.
.

09 January, 2010

Some Common Errors - 1 - using COUNT(*)

I will occassionally post "Some Common Errors" over the next few months.


1. Using a SELECT COUNT to test performance after adding an index.
Say a query has been running poorly, doing FullTableScans. The DBA adds an Index on the correct columns for the query predicates. Now, instead of running the whole query "as is" he substitutes all the columns of the SELECT clause with only a "COUNT(*)".
This is absolutely the wrong thing to do as Oracle may well just use the index and not need to access the table at all. The "COST" and the number of block gets of such an operation would be much smaller than an operation that has to access at least one table column not present in the index !

Here is a simple demo (I've excluded the portion about how I created the STORES_LIST table) :

This is the STORES_LIST Table :

SQL> REM Describe the table
SQL> select blocks, avg_row_len, num_rows, sample_size from user_tables where table_name = 'STORES_LIST';

BLOCKS AVG_ROW_LEN NUM_ROWS SAMPLE_SIZE
---------- ----------- ---------- -----------
4930 80 405884 405884

SQL> desc stores_list
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
STORE_ID NOT NULL NUMBER
COUNTRY NOT NULL VARCHAR2(30)
STATE NOT NULL VARCHAR2(23)
STORE_NAME NOT NULL VARCHAR2(128)
STORE_OWNER VARCHAR2(128)
START_DATE DATE

SQL>


The Table has 405,884 rows (with 100% sampling).

Currently, there is an Index on STORE_ID (which is a Unique Key). However, the user's query is not based on STORE_ID : (I've added some relevant statistics which the DBA/Developer might have looked at)

SQL> select country, state, store_name from stores_list where country = 'INDIA' and state like 'VI%';

23408 rows selected.

Elapsed: 00:00:00.50

Execution Plan
----------------------------------------------------------
Plan hash value: 3986898903

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13377 | 587K| 1347 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| STORES_LIST | 13377 | 587K| 1347 (1)| 00:00:17 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("COUNTRY"='INDIA' AND "STATE" LIKE 'VI%')


4899 consistent gets
4845 physical reads

(The DBA/Developer has possibly ignored some statistics ?)

The user requests the DBA's (or, the Developer's) assistance to "tune" the query. The DBA/Developer determines that an index on (COUNTRY,STATE) would be useful. He, then, acts accordingly :

SQL> REM The DBA creates this index
SQL> create index stores_list_cntry_stt_ndx on stores_list (country,state);

Index created.

SQL> select index_name, num_rows, sample_size, distinct_keys, leaf_blocks, clustering_factor
2 from user_indexes where table_name = 'STORES_LIST';

INDEX_NAME NUM_ROWS SAMPLE_SIZE DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- ------------- ----------- -----------------
STORES_LIST_STORE_ID_NDX 405884 405884 405884 869 5242
STORES_LIST_CNTRY_STT_NDX 405884 405884 253 1709 16629

SQL>


The DBA/Developer thinks he will get better performance. To test the user's query, he runs it again. However, he wishes to avoid displaying the 23,408 rows output (after all, who wants to wait for the terminal to finish displaying twenty thousand odd rows ?). He figures that he can run the same query, but put a count(*) or count(1) "wrapper" around it ! "Surely, it still has to fetch all the rows from the table", he says.
So, his "test" query is :

SQL> select count(1) from
2 (select country, state, store_name from stores_list where country = 'INDIA' and state like 'VI%');

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 32650011

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 59 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
|* 2 | INDEX RANGE SCAN| STORES_LIST_CNTRY_STT_NDX | 13377 | 261K| 59 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COUNTRY"='INDIA' AND "STATE" LIKE 'VI%')
filter("STATE" LIKE 'VI%')

88 consistent gets
102 physical reads


Once again, the DBA/Developer has ignored some information which he could have gleaned from the Execution Plan and the Statistics. All he sees is that execution time is down from 0.50 seconds to 0.04 seconds and that the numbers for consistent gets and physical reads from being in excess of 4,800 to around 100 only.
He is happy that he has "tuned" the query and informs the user that query runtime is now only 8% of the previous runtime !

The user now runs his own query. .....
.... (and, surely, you know why I posted this) .... there's a catch ! There is some performance improvement but not to the same scale !


SQL> select country, state, store_name from stores_list where country = 'INDIA' and state like 'VI%';

23408 rows selected.

Elapsed: 00:00:00.30

Execution Plan
----------------------------------------------------------
Plan hash value: 2588565413

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13377 | 587K| 609 (1)| 00:00:08 |
| 1 | TABLE ACCESS BY INDEX ROWID| STORES_LIST | 13377 | 587K| 609 (1)| 00:00:08 |
|* 2 | INDEX RANGE SCAN | STORES_LIST_CNTRY_STT_NDX | 13377 | | 59 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COUNTRY"='INDIA' AND "STATE" LIKE 'VI%')
filter("STATE" LIKE 'VI%')

911 consistent gets
1393 physical reads


Runtime has improved by only 40% --- it is still 60% of what it was before, not the 8% that the DBA/Developer claims.


What has gone wrong ?
The analysis for one.
The usage of a count(*) wrapper as well.

Let's take the second point -- about using a count(*) first. If you look closely at the Execution Plan, you can figure out that Oracle
a) Decides to read the index alone
b) Expects only 1 row as the final result set.

If the user's query was to fetch three columns of which only 2 columns were in the index, there is no way that the real result-set can be generated without going to the table to retrieve the values of the third column (STORE_NAME) that is not in the index. Those values are only in the table rows. Yet, Oracle doesn't go to the table at all !
Oracle sees the query as a request for a count(*) as being the final result. The optimizer figures out that it can use the Index to read all the ROWIDs and do a count against the index. It doesn't really need to know what the STORE_NAMEs are because STORE_NAME is not a query predicate. The optimizer can satisfy the query submitted by the DBA/Developer by using the Index alone !

The expected Row Count of 1 should have alerted the DBA/Developer ! A COUNT(*) operation returns 1 row -- a row that displays a count of rows ! It does NOT return 23,408 rows.

The optimizer has been smart (and correct) in generating an execution plan to satisfy what the DBA/Developer requested -- a count of rows, not the actual rows themselves !


Returning to the first point. What was wrong -- actually missing -- in the analysis. Many a times a person doesn't read all the output presented by an EXPLAIN PLAN (an example of which we just noted above) or all the Statistics presented by the AUTOTRACE.

In the user's first query execution, these statistics were presented :

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4899 consistent gets
4845 physical reads
0 redo size
544997 bytes sent via SQL*Net to client
998 bytes received via SQL*Net from client
48 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23408 rows processed


In his second query execution (after the index was added), these were the statistics :

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
911 consistent gets
1393 physical reads
0 redo size
544997 bytes sent via SQL*Net to client
998 bytes received via SQL*Net from client
48 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23408 rows processed


However, in the DBA/Developer's execution, the statistics were :

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
88 consistent gets
102 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


We've already seen what that "1 rows processed" means. It is the size of the final result set. The database server sent only 1 row to the client program -- sqlplus -- in the query executed by the DBA/Developer. However, it sent 23,408 rows in both executions by the user.

What else is significant ? Look at "bytes sent via SQL*Net to client" and "SQL*Net roundtrips to/from client" ! In both executions by the user, 545thousand bytes were sent in 48 round-trips. Both the database server and the client (sqlplus) incurred some slight (albeit, very very slight, considering modern microprocessor speeds) overheads in sending, receiving, presenting (and writing, if spooling to disk) 545kilobytes of data ! Furthermore, network latency impinged on each of the 48 round-trips. Assuming a latency of 2ms, that adds 98 ms. If this were a WAN link with a higher latency -- say 10ms or 15ms ? -- we are talking of higher elapsed time required to complete the result-set ! The DBA/Developer completely avoided both overheads -- the volume of data and the number of round-trips -- with his count(*) trick !


So, the next time you, as a DBA or a Developer tries to use a count(*) wrapper around a user's query, remember that the query you submit to the optimizer is *different* from that the user actually runs. The optimizer will identify that your requirements are not the same as the user's requirements and will generate a different execution plan which may well run much faster than what the user's session sees.

.
.
.

Adding a PK Constraint sets the key column to NOT NULL

Adding a Primary Key constraint on a column that even if not defined as NOT NULL does set the column to be a NOT NULL !


SQL> create table TEST_PK_TABLE as select owner, object_name, object_id, created from dba_objects where object_id is not null;

Table created.

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> create unique index TEST_PK_INDEX on TEST_PK_TABLE(object_id);

Index created.

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id);

Table altered.

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
CREATED DATE

SQL> -- Rem the OBJECT_ID column is now a NOT NULL !



What if the Column did contain one or more NULLs ? The Primary Key constraint definition would fail.


SQL> drop table TEST_PK_TABLE;

Table dropped.

SQL> create table TEST_PK_TABLE as select owner, object_name, object_id, created from dba_objects;

Table created.

SQL> select count(*) from TEST_PK_TABLE where object_id is null;

COUNT(*)
----------
1

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> create unique index TEST_PK_INDEX on TEST_PK_TABLE(object_id);

Index created.

SQL> -- Rem a Unique Index can be defined even with NULLs
SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id);
alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id)
*
ERROR at line 1:
ORA-01449: column contains NULL values; cannot alter to NOT NULL


SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL>


Defining the Constraint as a Disabled constraint also, obviously, doesn't touch the column.


SQL> alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id) disable;

Table altered.

SQL> desc TEST_PK_TABLE
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL>





Note : The above tests have been done in 10.2.0.4

.
.
.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com