Skip to Main Content
  • Questions
  • Execution plan changes after truncate and re-load

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Simon.

Asked: November 27, 2005 - 5:30 pm UTC

Last updated: December 24, 2005 - 12:27 pm UTC

Version: 10.1.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Can you explain why the execution plan for a query changes after truncating and reloading tables with exactly the same data ?

Here's my example :

--
-- Create Tables
--

CREATE TABLE CUSTOMER_DIM
( MONTH_KEY VARCHAR2 (6) NOT NULL ,
CUSTOMER_KEY NUMBER (12) NOT NULL ,
DATE_OPENED DATE NULL ,
DATE_CLOSED DATE NULL ,
ACTIVE_IND VARCHAR2 (1) NULL )
PARTITION BY RANGE (MONTH_KEY)
(PARTITION CUS_200507 VALUES LESS THAN ('200508'),
PARTITION CUS_200508 VALUES LESS THAN ('200509'),
PARTITION CUS_200509 VALUES LESS THAN ('200510'));

CREATE UNIQUE INDEX CUS_PK ON CUSTOMER_DIM (MONTH_KEY, CUSTOMER_KEY);
ALTER TABLE CUSTOMER_DIM ADD (CONSTRAINT CUS_PK PRIMARY KEY (MONTH_KEY, CUSTOMER_KEY));


CREATE TABLE ACCOUNT_PERFORMANCE_FACT
( MONTH_KEY VARCHAR2 (6) NOT NULL ,
ACCOUNT_KEY NUMBER (12) NOT NULL ,
CUSTOMER_KEY NUMBER (12) NOT NULL ,
DATE_LAST_ACTIVITY DATE NULL ,
GOOD_BAD_IND VARCHAR2 (1) NULL )
PARTITION BY RANGE (MONTH_KEY)
(PARTITION APF_200507 VALUES LESS THAN ('200508'),
PARTITION APF_200508 VALUES LESS THAN ('200509'),
PARTITION APF_200509 VALUES LESS THAN ('200510'));

CREATE UNIQUE INDEX APF_PK ON ACCOUNT_PERFORMANCE_FACT (MONTH_KEY, ACCOUNT_KEY) LOCAL;
ALTER TABLE ACCOUNT_PERFORMANCE_FACT ADD (CONSTRAINT APF_PK PRIMARY KEY (MONTH_KEY, ACCOUNT_KEY) USING INDEX LOCAL);


CREATE TABLE CARD_PERFORMANCE_FACT
( MONTH_KEY VARCHAR2 (6) NOT NULL ,
ACCOUNT_KEY NUMBER (12) NOT NULL ,
NUM_OF_PAYMENTS NUMBER (8) NULL ,
NUM_OF_REFUNDS NUMBER (8) NULL ,
NUM_OF_CREDITS NUMBER (8) NULL )
PARTITION BY RANGE (MONTH_KEY)
(PARTITION CPF_200507 VALUES LESS THAN ('200508'),
PARTITION CPF_200508 VALUES LESS THAN ('200509'),
PARTITION CPF_200509 VALUES LESS THAN ('200510'));

CREATE UNIQUE INDEX CPF_PK ON CARD_PERFORMANCE_FACT (MONTH_KEY, ACCOUNT_KEY) LOCAL;
ALTER TABLE CARD_PERFORMANCE_FACT ADD (CONSTRAINT CPF_PK PRIMARY KEY (MONTH_KEY, ACCOUNT_KEY) USING INDEX LOCAL);

--
-- Insert data
--

truncate table CUSTOMER_DIM;
truncate table ACCOUNT_PERFORMANCE_FACT;
truncate table CARD_PERFORMANCE_FACT;

insert into ACCOUNT_PERFORMANCE_FACT
select '200507',
rownum,
mod(rownum, 25) + 1,
to_date('20041231', 'yyyymmdd') + mod(rownum, 30),
'Y'
from (select 1
from dba_objects
where rownum < 1001),
(select 1
from dba_objects
where rownum < 1001);

insert into ACCOUNT_PERFORMANCE_FACT
select '200508',
account_key,
customer_key,
date_last_activity,
good_bad_ind
from ACCOUNT_PERFORMANCE_FACT
where month_key = '200507';

insert into ACCOUNT_PERFORMANCE_FACT
select '200509',
account_key,
customer_key,
date_last_activity,
good_bad_ind
from ACCOUNT_PERFORMANCE_FACT
where month_key = '200507';

insert into CUSTOMER_DIM
select month_key,
account_key customer_key,
sysdate,
null,
case when account_key < 30 then 'A' else 'B' end
from ACCOUNT_PERFORMANCE_FACT
where account_key < 5001;

insert into CARD_PERFORMANCE_FACT
select month_key,
account_key,
mod(rownum, 10),
mod(rownum, 15),
mod(rownum, 25)
from ACCOUNT_PERFORMANCE_FACT
where mod(account_key, 10) = 0;

commit;

exec dbms_stats.gather_table_stats(tabname=>'CUSTOMER_DIM', ownname=>user, cascade=>true);
exec dbms_stats.gather_table_stats(tabname=>'ACCOUNT_PERFORMANCE_FACT', ownname=>user, cascade=>true);
exec dbms_stats.gather_table_stats(tabname=>'CARD_PERFORMANCE_FACT', ownname=>user, cascade=>true);

--
-- End of Inserts
--

The explain plans for my two queries now look like this :

Query 1
=======

explain plan for
select a.*,
b.*
from CUSTOMER_DIM a,
ACCOUNT_PERFORMANCE_FACT b
where a.CUSTOMER_KEY = b.CUSTOMER_KEY
and a.MONTH_KEY = b.MONTH_KEY
and a.ACTIVE_IND = 'A'

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1498K| 64M| 2521 (15)| 00:00:31 | | |
| 1 | PARTITION RANGE ALL| | 1498K| 64M| 2521 (15)| 00:00:31 | 1 | 3 |
|* 2 | HASH JOIN | | 1498K| 64M| 2521 (15)| 00:00:31 | | |
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K| 14 (15)| 00:00:01 | 1 | 3 |
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 2997K| 68M| 2368 (10)| 00:00:29 | 1 | 3 |
----------------------------------------------------------------------------------------------------------------

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

2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')


Query 2
=======

explain plan for
select a.*,
b.*,
c.*
from CUSTOMER_DIM a,
ACCOUNT_PERFORMANCE_FACT b,
CARD_PERFORMANCE_FACT c
where a.CUSTOMER_KEY = b.CUSTOMER_KEY
and a.MONTH_KEY = b.MONTH_KEY
and a.ACTIVE_IND = 'A'
and b.ACCOUNT_KEY = c.ACCOUNT_KEY
and b.MONTH_KEY = c.MONTH_KEY


-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 158K| 9M| | 9544 (6)| 00:01:55 | | |
| 1 | PARTITION RANGE ALL | | 158K| 9M| | 9544 (6)| 00:01:55 | 1 | 3 |
|* 2 | HASH JOIN | | 158K| 9M| 3144K| 9544 (6)| 00:01:55 | | |
| 3 | TABLE ACCESS FULL | CARD_PERFORMANCE_FACT | 301K| 5889K| | 207 (11)| 00:00:03 | 1 | 3 |
|* 4 | HASH JOIN | | 1498K| 64M| | 2521 (15)| 00:00:31 | | |
|* 5 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K| | 14 (15)| 00:00:01 | 1 | 3 |
| 6 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 2997K| 68M| | 2368 (10)| 00:00:29 | 1 | 3 |
-------------------------------------------------------------------------------------------------------------------------

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

2 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")
4 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
5 - filter("A"."ACTIVE_IND"='A')


Now re-run the inserts script. This time the explain plans look like this :

Query 1
=======

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17380 | 763K| 2520 (15)| 00:00:31 | | |
| 1 | PARTITION RANGE ALL| | 17380 | 763K| 2520 (15)| 00:00:31 | 1 | 3 |
|* 2 | HASH JOIN | | 17380 | 763K| 2520 (15)| 00:00:31 | | |
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 87 | 1827 | 14 (15)| 00:00:01 | 1 | 3 |
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 2996K| 68M| 2368 (10)| 00:00:29 | 1 | 3 |
----------------------------------------------------------------------------------------------------------------

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

2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')


Query 2
=======

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1816 | 115K| 4330 (10)| 00:00:52 | | |
| 1 | NESTED LOOPS | | 1816 | 115K| 4330 (10)| 00:00:52 | | |
| 2 | PARTITION RANGE ALL | | 17380 | 763K| 2520 (15)| 00:00:31 | 1 | 3 |
|* 3 | HASH JOIN | | 17380 | 763K| 2520 (15)| 00:00:31 | | |
|* 4 | TABLE ACCESS FULL | CUSTOMER_DIM | 87 | 1827 | 14 (15)| 00:00:01 | 1 | 3 |
| 5 | TABLE ACCESS FULL | ACCOUNT_PERFORMANCE_FACT | 2996K| 68M| 2368 (10)| 00:00:29 | 1 | 3 |
| 6 | PARTITION RANGE ITERATOR | | 1 | 20 | 1 (0)| 00:00:01 | KEY | KEY |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| CARD_PERFORMANCE_FACT | 1 | 20 | 1 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX UNIQUE SCAN | CPF_PK | 1 | | 0 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------

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

3 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
4 - filter("A"."ACTIVE_IND"='A')
8 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")



Notice how for Query 1, the 'Rows' has dropped from 1.5 million to 17,000 - this presumably explains why Query 2 has changed to use Nested Loops (because the lower number of rows returned by the first join makes a NL join more attractive).

Following the reload, Query 2 takes about twice as long to execute as it did on the first run.

Any help would be much appreciated.

Thanks,
Simon.


and Tom said...

you don't even need the truncate, all you need to do is regather statistics.

In 10g, the "method_opt" parameter defaults to a "size auto". After you ran a query - the database remembered the predicates and updated a dictionary table sys.col_usage$. Then, the next time you ran dbms_stats, it queried that table to find out what columns should have histograms collected automagically based on "past query workload". It looked at your predicates and said "hmm, these columns should have histograms".

I can reproduce your experience without the truncate. If you do not want this behaviour, you would not use AUTO as the method_opt default - you would be more explicit.



ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CUSTOMER_DIM', ownname=>user, cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'ACCOUNT_PERFORMANCE_FACT', ownname=>user, cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CARD_PERFORMANCE_FACT', ownname=>user, cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> -- End of Inserts
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> delete from plan_table;

7 rows deleted.

ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*
4 from CUSTOMER_DIM a,
5 ACCOUNT_PERFORMANCE_FACT b
6 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
7 and a.MONTH_KEY = b.MONTH_KEY
8 and a.ACTIVE_IND = 'A';

Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1977428117

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1500K| 64M| 2924
| 1 | PARTITION RANGE ALL| | 1500K| 64M| 2924
|* 2 | HASH JOIN | | 1500K| 64M| 2924
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K| 16
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836
----------------------------------------------------------------------------------------------------------------

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

2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')

17 rows selected.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> delete from plan_table;

5 rows deleted.

ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*,
4 c.*
5 from CUSTOMER_DIM a,
6 ACCOUNT_PERFORMANCE_FACT b,
7 CARD_PERFORMANCE_FACT c
8 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
9 and a.MONTH_KEY = b.MONTH_KEY
10 and a.ACTIVE_IND = 'A'
11 and b.ACCOUNT_KEY = c.ACCOUNT_KEY
12 and b.MONTH_KEY = c.MONTH_KEY;

Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1330301989

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 147K| 9344K|
| 1 | PARTITION RANGE ALL | | 147K| 9344K|
|* 2 | HASH JOIN | | 147K| 9344K|
| 3 | TABLE ACCESS FULL | CARD_PERFORMANCE_FACT | 300K| 5859K|
|* 4 | HASH JOIN | | 1500K| 64M|
|* 5 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K|
| 6 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M|
-------------------------------------------------------------------------------------------------------------------------

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

2 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")
4 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
5 - filter("A"."ACTIVE_IND"='A')

20 rows selected.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CUSTOMER_DIM', ownname=>user, cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'ACCOUNT_PERFORMANCE_FACT', ownname=>user, cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats(tabname=>'CARD_PERFORMANCE_FACT', ownname=>user, cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> -- End of Inserts
ops$tkyte@ORA10GR1> --
ops$tkyte@ORA10GR1> delete from plan_table;

7 rows deleted.

ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*
4 from CUSTOMER_DIM a,
5 ACCOUNT_PERFORMANCE_FACT b
6 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
7 and a.MONTH_KEY = b.MONTH_KEY
8 and a.ACTIVE_IND = 'A';

Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1977428117

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17400 | 764K| 2924
| 1 | PARTITION RANGE ALL| | 17400 | 764K| 2924
|* 2 | HASH JOIN | | 17400 | 764K| 2924
|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 87 | 1827 | 16
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836
----------------------------------------------------------------------------------------------------------------

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

2 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
3 - filter("A"."ACTIVE_IND"='A')

17 rows selected.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> delete from plan_table;

5 rows deleted.

ops$tkyte@ORA10GR1> explain plan for
2 select a.*,
3 b.*,
4 c.*
5 from CUSTOMER_DIM a,
6 ACCOUNT_PERFORMANCE_FACT b,
7 CARD_PERFORMANCE_FACT c
8 where a.CUSTOMER_KEY = b.CUSTOMER_KEY
9 and a.MONTH_KEY = b.MONTH_KEY
10 and a.ACTIVE_IND = 'A'
11 and b.ACCOUNT_KEY = c.ACCOUNT_KEY
12 and b.MONTH_KEY = c.MONTH_KEY;

Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1329693311

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1817 | 115K| 3189
| 1 | PARTITION RANGE ALL | | 1817 | 115K| 3189
|* 2 | HASH JOIN | | 1817 | 115K| 3189
|* 3 | HASH JOIN | | 17400 | 764K| 2924
|* 4 | TABLE ACCESS FULL| CUSTOMER_DIM | 87 | 1827 | 16
| 5 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836
| 6 | TABLE ACCESS FULL | CARD_PERFORMANCE_FACT | 300K| 5859K| 256 -----------------------------------------------------------------------------------------------------------------

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

2 - access("B"."MONTH_KEY"="C"."MONTH_KEY" AND "B"."ACCOUNT_KEY"="C"."ACCOUNT_KEY")
3 - access("A"."MONTH_KEY"="B"."MONTH_KEY" AND "A"."CUSTOMER_KEY"="B"."CUSTOMER_KEY")
4 - filter("A"."ACTIVE_IND"='A')

20 rows selected.

ops$tkyte@ORA10GR1>


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Simon, November 27, 2005 - 11:21 pm UTC

Excellent, thanks Tom.

Given that histograms are supposed to help the optimizer determine the cardinality of each join step in a plan, I was surprised to see that, following the second analyze, the Cardinality values shown in the plans got worse.

ie. the join between CUSTOMER_DIM and ACCOUNT_PERFORMANCE_DIM will return 3 million rows; after the the initial analyze the plans show an expected 1.5 million rows for this join - after the second analyze, that's dropped to 17,000.

Still, at least now I can work around this and get the plan I want.

Tom Kyte
November 28, 2005 - 7:31 am UTC

it got these cardinalities dead on:

|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 87 | 1827 | 16
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836


whereas before it got these before the histogram - the 7500 is very far off:

|* 3 | TABLE ACCESS FULL| CUSTOMER_DIM | 7500 | 153K| 16
| 4 | TABLE ACCESS FULL| ACCOUNT_PERFORMANCE_FACT | 3000K| 68M| 2836


it was the computation of the join cardinality that got botched - because the optimizer did not understand that when "active-ind" = 'A', every needed customer_key/month_key would be in the set from customer_dim. In fact, that they were is sort of strange. If this is versioned information, we might consider keeping the active records separate from the inactive?

sys.col_usage$

Marcio Portes, December 08, 2005 - 2:26 pm UTC

How much time does Oracle take to update sys.col_usage$ ?
I can't figure this out. I tried out a test from scratch and doesn't work.

here is the test (9.2.0.3 over HP-UX 11.11)

drop table t;

create table t nologging as
select level a, 5 x
from dual connect by level <= 10000
/
commit;

alter table t logging;

create index t_idx on t (x);

update t
set x = rownum
where rownum <= 4 and x = 5;

update t
set x = 9991+rownum
where rownum <= 5 and x = 5;

select x, count(*)
from t
group by x
/

create or replace
procedure p ( p_x in number )
is
begin
for x in ( select * from t where x = p_x )
loop
null;
end loop;
end;
/
show error

begin
for i in 1 .. 10
loop
p( i );
end loop;
end;
/


column object_id new_value obj
column object_name format a30

select object_name, object_id
from user_objects
where object_name = 'T'
/

select *
from sys.col_usage$
where obj# = &obj
/

Therefore when I collect stats for the table I got wrong histograms. Suppose that's missing col_usage$ data for correct information.

For this data

X COUNT(*)
------------- -------------
1 1
2 1
3 1
4 1
5 9991
9992 1
9993 1
9994 1
9995 1
9996 1

I've got this.

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE QDT_ROWS
--------------- --------------- -------------- -------------
X 0 1 0
X 1 9996 1

I tried gather stats as follows.

begin
dbms_stats.gather_table_stats(
user, 'T',
cascade => true,
method_opt => 'for columns x size auto');
end;
/

and

begin
dbms_stats.delete_table_stats( user, 'T' );

dbms_stats.gather_table_stats(
user, 'T',
cascade => true,
method_opt => 'for all indexed columns size auto');
end;
/

Regards,


Tom Kyte
December 09, 2005 - 1:25 am UTC

in 9i, must be "some schedule" (not documented).  In 10g, it is rather immediate - when you gather the stats:

...
ops$tkyte@ORA10GR1> select object_name, object_id
  2    from user_objects
  3   where object_name = 'T'
  4  /

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
T                                   53795

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select *
  2    from sys.col_usage$
  3   where obj# = &obj
  4  /
old   3:  where obj# = &obj
new   3:  where obj# =      53795

no rows selected

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size AUTO' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select *
  2    from sys.col_usage$
  3   where obj# = &obj
  4  /
old   3:  where obj# = &obj
new   3:  where obj# =      53795

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS
---------- ---------- -------------- -------------- -----------------
RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
----------- ---------- ---------- ---------
     53795          2              1              0                 0
          0          0          0 09-DEC-05


ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> column column_name format a5
ops$tkyte@ORA10GR1> select column_name, endpoint_number, endpoint_value from user_tab_histograms where table_name
  2  = 'T';

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE
----- --------------- --------------
X                   1              1
X                   2              2
X                   3              3
X                   4              4
X                9995              5
X                9996           9992
X                9997           9993
X                9998           9994
X                9999           9995
X               10000           9996
A                   0              1
A                   1          10000

12 rows selected.
 

Why couldn't reproduce it on 10g 10.2.0.1.0?

A reader, December 09, 2005 - 1:07 pm UTC

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 9 09:45:17 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> drop table t;

create table t nologging as
select level a, 5 x 
  from dual connect by level <= 10000 drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SQL>   2    3  
  4  
SQL> 
SQL> 
SQL> /

Table created.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 X                                                  NUMBER

SQL> column object_name format a30;
SQL>  select object_name, object_id
     from user_objects where object_name='T';  2  

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
T                                   16926

SQL> select * from sys.col_usage$ where obj#=16926;

no rows selected

SQL> exec dbms_stats.gather_table_stats( user, 'T',method_opt=>'for all columns size AUTO' );

PL/SQL procedure successfully completed.

SQL>  select * from sys.col_usage$ where obj#=16926;

no rows selected

SQL> select column_name, endpoint_number, endpoint_value from 
user_tab_histograms where table_name  2  ='T';

COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
A
              0              1

X
              0              5

A
              1          10000


COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
X
              1              5


SQL> show parameter statistic

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
SQL> alter session set statistics_level=all;

Session altered.

SQL>  select * from sys.col_usage$ where obj#=16926;

no rows selected

SQL>  exec dbms_stats.gather_table_stats( user, 'T',method_opt=>'for all columns size AUTO' );

PL/SQL procedure successfully completed.

SQL> select * from sys.col_usage$ where obj#=16926;

no rows selected

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> 
 

Tom Kyte
December 09, 2005 - 1:18 pm UTC

first thing I ran it in was 10gr2 - it "worked for me"


ops$tkyte@ORA10GR2> select *
  2    from sys.col_usage$
  3   where obj# = &obj
  4  /
old   3:  where obj# = &obj
new   3:  where obj# =      55450

no rows selected

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> begin
  2    dbms_stats.gather_table_stats(
  3        user, 'T',
  4        cascade => true,
  5        method_opt => 'for columns x size auto');
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select *
  2    from sys.col_usage$
  3   where obj# = &obj
  4  /
old   3:  where obj# = &obj
new   3:  where obj# =      55450

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS
---------- ---------- -------------- -------------- -----------------
RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
----------- ---------- ---------- ---------
     55450          2              1              0                 0
          0          0          0 09-DEC-05


ops$tkyte@ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

col_usage$

Mark A. Williams, December 09, 2005 - 9:05 pm UTC

Not sure this is helpful, but in order to see the information in sys.col_usage$ using the example presented here, I had to do this:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

SQL> drop table t purge;

Table dropped.

SQL> create table t as
  2  select   level a,
  3           5 x
  4  from     dual
  5  connect by level <= 10000;

Table created.

SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL> select   *
  2  from     sys.col_usage$
  3  where    obj#=(select   object_id
  4                 from     user_objects
  5                 where    object_name='T'
  6  );

no rows selected

SQL> select a, x from t where x = 0;

no rows selected

SQL> select   *
  2  from     sys.col_usage$
  3  where    obj#=(select   object_id
  4                 from     user_objects
  5                 where    object_name='T'
  6  );

no rows selected

SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL> select   *
  2  from     sys.col_usage$
  3  where    obj#=(select   object_id
  4                 from     user_objects
  5                 where    object_name='T'
  6  );

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS
---------- ---------- -------------- -------------- ----------------- ----------- ----------
NULL_PREDS TIMESTAMP
---------- ---------
     67136          2              1              0                 0           0          0
         0 09-DEC-05


1 row selected.

SQL>

Once I had done that, the information was there...

- Mark 

Thanks to Mark and Tom!!

A reader, December 10, 2005 - 1:24 am UTC

Don't know why it worked for Tom's environment.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 9 21:54:35 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> drop table t purge;

Table dropped.

SQL> create table t
  2  as select level a, 
  3  5 x
  4  from dual
  5  connect by level <=10000;

Table created.

SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL> select *
  2  from sys.col_usage$
  3  where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> select a, x from t where x=0;

no rows selected

SQL> select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL>  select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS
---------- ---------- -------------- -------------- -----------------
RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
----------- ---------- ---------- ---------
     16941          2              1              0                 0
          0          0          0 09-DEC-05


SQL> drop table t purge;

Table dropped.

SQL> create table t as select level a, 5 x from dual connect by level <=10000;

Table created.

SQL> select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> exec dbms_stats.gather_table_stats(user, 'T',  cascade => true, method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL>  select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL>  select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> exec dbms_stats.gather_table_stats(user, 'T',  cascade => true, method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL> select * from sys.col_usage$
  2  where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> select count(*) from t;

  COUNT(*)
----------
     10000

SQL>  select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL>  exec dbms_stats.gather_table_stats(user, 'T',  cascade => true, method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL>  select * from sys.col_usage$
  2  where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> select * from t where rownum<2;

         A          X
---------- ----------
         1          5

SQL> exec dbms_stats.gather_table_stats(user, 'T',  cascade => true, method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL>  select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

no rows selected

SQL> select * from t where a<3;

         A          X
---------- ----------
         1          5
         2          5

SQL>  exec dbms_stats.gather_table_stats(user, 'T',  cascade => true, method_opt=>'for all columns size AUTO');

PL/SQL procedure successfully completed.

SQL> select * from sys.col_usage$
  2   where obj#=(select object_id from user_objects where object_name='T');

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS
---------- ---------- -------------- -------------- -----------------
RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
----------- ---------- ---------- ---------
     16942          1              0              0                 0
          1          0          0 09-DEC-05


SQL> 


 

col_usage$

Marcio Portes, December 11, 2005 - 8:05 pm UTC

Guess I've got it. Tom could you please validade this? thanks.
The thoughts below are assumption, I don't have anything to support them.

When you gather stats at the first time, the col_usage$ looks like ready to go (somehow), thus as of second gather stats on the col_usage$ gets populated. This works even if 9i.

Regards,

ops$ora920@LNX920> select x, count(*)
2 from t
3 group by x
4 /

X COUNT(*)
------------- -------------
1 1
2 1
3 1
4 1
5 9991
9992 1
9993 1
9994 1
9995 1
9996 1

10 rows selected.

ops$ora920@LNX920>
ops$ora920@LNX920> create or replace
2 procedure gather_stats_t
3 is
4 begin
5 dbms_stats.delete_table_stats( user, 't' );
6 dbms_stats.gather_table_stats(
7 user,
8 't',
9 method_opt => 'for all columns size auto',
10 cascade => true
11 );
12 end;
13 /

Procedure created.

ops$ora920@LNX920>
ops$ora920@LNX920> create or replace
2 procedure p ( p_x in number )
3 is
4 begin
5 for x in ( select * from t where x = p_x )
6 loop
7 null;
8 end loop;
9 end;
10 /

Procedure created.

ops$ora920@LNX920> show error
No errors.
ops$ora920@LNX920>
ops$ora920@LNX920> exec gather_stats_t

PL/SQL procedure successfully completed.

ops$ora920@LNX920>
ops$ora920@LNX920> select cu.*
2 from sys.col_usage$ cu, user_objects uo
3 where obj# = object_id
4 and object_name = 'T'
5 /

no rows selected

ops$ora920@LNX920>
ops$ora920@LNX920> exec p( 10 )

PL/SQL procedure successfully completed.

ops$ora920@LNX920>
ops$ora920@LNX920> exec gather_stats_t

PL/SQL procedure successfully completed.

ops$ora920@LNX920>
ops$ora920@LNX920> select cu.*
2 from sys.col_usage$ cu, user_objects uo
3 where obj# = object_id
4 and object_name = 'T'
5 /

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS
------------- ------------- -------------- -------------- ----------------- ------------- ------------- -------------
TIMESTAMP
-------------------
31757 2 1 0 0 0 0 0
11/12/2005 23:08:15


1 row selected.

ops$ora920@LNX920>
ops$ora920@LNX920>
ops$ora920@LNX920> set echo off
ops$ora920@LNX920> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

5 rows selected.



Tom Kyte
December 12, 2005 - 7:48 am UTC

yes, that is what I demonstrated, col_usage$ is populated after you run queries that make use of particular types of predicates against the table itself.

Trying to see the effect of "Column size auto"

Jagjeet Singh, December 24, 2005 - 7:19 am UTC

Hi,

I was just trying to see the effect of "column size auto" 
but could not make it possible using 9.2.0 with this test 
case as  sys.col_usage$ not having any records for this table


I created a table and analyze it with "columns size auto"
and randomly issued inserts, updates and  deletes.


----------- 

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ind_t on t (owner,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size auto ',cascade=> true );

PL/SQL procedure successfully completed.

SQL> select count(*),column_name from user_tab_histograms
  2  where table_name = 'T'  group by column_name;

  COUNT(*) COLUMN_NAME
---------- ------------------------------
         2 CREATED
         2 DATA_OBJECT_ID
         2 GENERATED
         2 LAST_DDL_TIME
         2 OBJECT_ID
         2 OBJECT_NAME
         2 OBJECT_TYPE
         2 OWNER
         2 SECONDARY
         2 STATUS
         2 SUBOBJECT_NAME
         2 TEMPORARY
         2 TIMESTAMP

13 rows selected.

SQL> create view v as select column_name from user_tab_columns
  2  where table_name = 'T'
  3  order by dbms_random.value;

View created.

SQL> 
SQL> alter table t monitoring;

Table altered.

SQL> get a
  1  declare
  2  v_col   varchar2(30);
  3  v_action      int ;
  4  v_random   int;
  5  begin
  6  for i in 1..1000 loop
  7  --
  8    v_action    := round(dbms_random.value(1,3));
  9    v_random := round(dbms_Random.value(1,500));

      ## filling the v_action for any of one value from 1,2,3
      ## 1 = delete , 2 = insert , 3 = update

      ## v_random for getting random no. of records for 
      ##   inserts,updates,deletes       


 10  --
 11    select column_name into v_col from v where rownum = 1 ;
   
        ## selecting a column of T randomly    
 12  --
 13   if v_action = 1  then    --####  delete some records
 14  --
 15   execute immediate ' delete from t where :x = :y and rownum < :z '
 16                       using v_col,v_col,v_random;
 17  --
 18   elsif v_action = 2 then  ---###  insert some records
 19  --
 20    execute immediate ' Insert into t select * from t where :x = :y and rownum < :z '
 21                        using v_col,v_col, v_random;
 22  --
 23  elsif v_action = 3 then  ---###  update some records
 24  --
 25    execute immediate ' update t set '||v_col||' = '||v_col||' where rownum < :y '
 26                        using  v_random;
 27  --
 28  end if;
 29  --
 30  end loop;
 31  ---
 32* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> exec p ('select * from user_tab_modifications')


TABLE_NAME                     : T
PARTITION_NAME                 :
SUBPARTITION_NAME              :
INSERTS                        : 60250
UPDATES                        : 28521
DELETES                        : 30798
TIMESTAMP                      : 24-DEC-05
TRUNCATED                      : NO



PL/SQL procedure successfully completed.

SQL> select object_id from user_objects where object_name = 'T';

 OBJECT_ID
----------
     24027

SQL> select count(*) from sys.col_usage$ where obj# = 24027;

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

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size auto ',cascade=> true );

PL/SQL procedure successfully completed.

SQL> select count(*),column_name from user_tab_histograms
  2  where table_name = 'T'
  3  group by column_name;

  COUNT(*) COLUMN_NAME
---------- ------------------------------
         2 CREATED
         2 DATA_OBJECT_ID
         2 GENERATED
         2 LAST_DDL_TIME
         2 OBJECT_ID
         2 OBJECT_NAME
         2 OBJECT_TYPE
         2 OWNER
         2 SECONDARY
         2 STATUS
         2 SUBOBJECT_NAME
         2 TEMPORARY
         2 TIMESTAMP

13 rows selected.


Even I bounced the database but could not get the desired result. 

Tom Kyte
December 24, 2005 - 9:25 am UTC

size auto, undocumented, ever changing.  but only populated when you have queries running in CBO .  

And your queries actually did not touch any columns:

delete from t where :x = :y and rownum < :z '
is like saying:
delete from t where 42 = 55 and rownum < 10;

Insert into t select * from t where :x = :y and rownum < :z 
is
insert into t select * from t where 42 = 55 and rownum < 10;

update t set '||v_col||' = '||v_col||' where rownum < :y '
is
update t set c1 = c1 where rownum < 10;

(you set C1, so what?  you searched by ROWNUM - you used no columns to find rows...)

Here is a small example, we'll skew some data for sure:

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select 99 id, a.*
  4    from all_objects a;

Table created.

ops$tkyte@ORA9IR2> update t set id = rownum where rownum <= 10;

10 rows updated.

<b>id, very skewed...</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(id);

Index created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt =>'for all columns size AUTO' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select * from sys.col_usage$ where obj# = (select object_id from user_objects where object_name = 'T' );

no rows selected

<b>that is to be expected of course, we haven't touched the table as yet.  Therefore, "size 1" histograms are generated</b>

ops$tkyte@ORA9IR2> select column_name, count(*)
  2    from user_tab_histograms
  3   where column_name in ( 'ID', 'OBJECT_NAME', 'OBJECT_ID' )
  4     and table_name = 'T'
  5   group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
ID                       2
OBJECT_ID                2
OBJECT_NAME              2

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2          for x in ( select * from t where object_name like '%X%' )
  3          loop
  4                  exit;
  5          end loop;
  6          for x in ( select * from t where object_id = 42 )
  7          loop
  8                  exit;
  9          end loop;
 10          for x in ( select * from t where id = 42 )
 11          loop
 12                  exit;
 13          end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

<b>Now, we did some queries with predicates.... we gather stats again:</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt =>'for all columns size AUTO' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select * from sys.col_usage$ where obj# = (select object_id from user_objects where object_name = 'T' );

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS
---------- ---------- -------------- -------------- -----------------
RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
----------- ---------- ---------- ---------
     36547          1              1              0                 0
          0          0          0 24-DEC-05

     36547          3              0              0                 0
          0          1          0 24-DEC-05

     36547          5              1              0                 0
          0          0          0 24-DEC-05

<b>we can see the three predicates on the three columns resulted in rows for columns 1, 3, and 5 - two equality predicates and one like predicate.  When we look at the generated statistics this time:</b>

ops$tkyte@ORA9IR2> select column_name, count(*)
  2    from user_tab_histograms
  3   where column_name in ( 'ID', 'OBJECT_NAME', 'OBJECT_ID' )
  4     and table_name = 'T'
  5   group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
ID                      11
OBJECT_ID                2
OBJECT_NAME            201


<b>we see radically different results....</b>
 

Great but ..

Jagjeet Singh, December 24, 2005 - 9:46 am UTC

Thanks for answering this ..

but I have some doubts.

I am using bind variables. Do you mean if we use bind variables then CBO will have no idea which column is being
used.

if yes then what about bind variable peeking ?

And if not then

in Insert it would be ..

insert into t select * from t where <<column_name>> = <<column_name>> and rownum.

It is selecting a column from View V.

for updating :
It is ok that there is only prediction with rownum.

for deleting :
again it would be .. like insertion.


Regards.
Js

Tom Kyte
December 24, 2005 - 12:27 pm UTC

you are binding CONSTANTS, you are comparing LITERAL to LITERAL

You haven't referenced a single solitary column in your queries at all (not in the predicate)


if you had coded:

delete from t where COLUMN_NAME = :y and rownum < :z
Insert into t select * from t where COLUMN_NAME = :y and rownum < :z
update t set '||v_col||' = '||v_col||' where COLUMN_NAME < :y '


then you would have. You did not bind "a column name" you bound LITERAL STRINGS

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.