| What could be the reason [message #58898] |
Sat, 11 October 2003 01:37  |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
|
select count(1) from tab2; is taking long time and its doing full table scan. what could be the reason for this. this is a very big table with 100 fields and 800,000 rows in it.
|
|
|
|
| Re: What could be the reason [message #58900 is a reply to message #58898] |
Sat, 11 October 2003 03:57  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Kapil,
Post your query and autotrace here pls. Do you have a unique index on this table ? Have you analyzed the table and index ? Why not count(*) ?
Also See my earlier posting about count(*)
-Thiru
|
|
|
|
|
|
| Re: What could be the reason [message #58902 is a reply to message #58901] |
Sat, 11 October 2003 09:25 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
yes, i do have a unique key index on that table on first 2 fields, i did run this query "SELECT COUNT(1) FROM TAB2" b4 and after running this "DBMS_STATS.GATHER_SCHEMA_STATS()". DON'T U THINK WITH WILL COMPUTE STATISTICS OF ALL SCHEMA OBJECTS?
PLEASE let me know if i need to run anything else beside this . Thanks
|
|
|
|
| Re: What could be the reason [message #58903 is a reply to message #58902] |
Sat, 11 October 2003 09:43 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Lets just do this
desc tab2;
Make sure you have atleast one NOT NULL column that is indexed. Unique key index is not necessary..that was a typo.
analzye table tab2 compute statistics;
set autotrace on explain
select count(*) from tab2;
Let me know the results.
Eg)
thiru@9.2.0:SQL>create table t3(x int not null);
Table created.
thiru@9.2.0:SQL>insert into t3 select rownum from all_objects where rownum <=10;
10 rows created.
thiru@9.2.0:SQL>commit;
Commit complete.
thiru@9.2.0:SQL>create index t3_idx on t3(x);
Index created.
thiru@9.2.0:SQL>analyze table t3 compute statistics;
Table analyzed.
thiru@9.2.0:SQL>set autotrace on explain
thiru@9.2.0:SQL>select count(1) from t3;
COUNT(1)
----------
10
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'T3_IDX' (NON-UNIQUE) (Cost=1 Card=10)
thiru@9.2.0:SQL>select count(*) from t3;
COUNT(*)
----------
10
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'T3_IDX' (NON-UNIQUE) (Cost=1 Card=10)
thiru@9.2.0:SQL>
Thiru
|
|
|
|
| Re: What could be the reason [message #58904 is a reply to message #58903] |
Sat, 11 October 2003 10:01 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
Sorry, Thiru.
Well the columns i created a unique key index are NOT NULL Columns. its doing full table scan for the query "select count(1) from tab". this table has 100 fields with a clob as one of the fields
|
|
|
|
| Re: What could be the reason [message #58905 is a reply to message #58904] |
Sat, 11 October 2003 10:12 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
|
Even after analyzing the object, its doing the full table scan, i analyzed the indexex, table with compute statistics. even after that its doing the full table scan.
|
|
|
|
| Re: What could be the reason [message #58906 is a reply to message #58904] |
Sat, 11 October 2003 10:14 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Kapil,
Why dont you copy & paste your screen here . I want to see exactly whats going on. I had no problem counting a table with CLOB field using Index scan. Number of fields here doesnt matter . Oracle just needs a not null column that is indexed.
BTW, 100 columns in 1 table ? !! Maybe you are joking ?
-Thiru
|
|
|
|
| Re: What could be the reason [message #58907 is a reply to message #58906] |
Sat, 11 October 2003 10:16 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
|
sorry, Even after analyzing the object, its doing the full table scan, i analyzed the indexex, table with compute statistics. even after that its doing the full table scan.
|
|
|
|
| Re: What could be the reason [message #58908 is a reply to message #58907] |
Sat, 11 October 2003 10:19 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
|
Sorry, I wont be able to proceed further until you can copy & paste your session here . I want to see what you did and what the CBO is doing. If you could do as per the example as given earlier, its better.
|
|
|
|
| Re: What could be the reason [message #58909 is a reply to message #58908] |
Sat, 11 October 2003 10:24 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
Thiru,
Well. i tried all these yesterday at work place, but dint help me at all. thats why the reason i wasn't able to copy and paste my session. but trust me. i do have index on NOT NULL columns. i did analyze and compute my stats on both indexes and table, but did not help. thats main problem, i may able to copy and paste the session on monday. Thanks for help.. ur doing gr88 job. Thanks again.
|
|
|
|
| Re: What could be the reason [message #58910 is a reply to message #58909] |
Sat, 11 October 2003 10:29 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
I knew you werent at your desk ;) BTW, did you try hinting the CBO to use the Index via /*+ INDEX( ) */ hint ?
Lets see this on Monday. Have a nice weekend now!
-Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
