How can I get COUNT (col) ... GROUP BY to use the index?
I have a table (col1, col2, ...) with an index (col1, col2, ...). There are millions of rows in the table and I want to run a query:
SELECT col1, COUNT(col2) WHERE col1 NOT IN (<couple of exclusions>) GROUP BY col1
Unfortunately, this results in a full table scan that takes over a minute. Is there a way to get the oracle to use an index on the columns to return results faster?
EDIT:
more specifically, I run the following query:
SELECT owner, COUNT(object_name) FROM all_objects GROUP BY owner
and there is an index on SYS.OBJ$
( SYS.I_OBJ2
) that indexes the columns owner#
and name
; I believe I should be using this index in the query and not a full table scanSYS.OBJ$
a source to share
I had the opportunity to play around with this, and my previous comments regarding NOT IN are a red herring in this case. The key point is the presence of NULL, or rather, whether the indexed columns are forced to have NOT NULL constraints.
It depends on the version of the database you are using, because the optimizer gets smarter with each version. I am using 11gR1 and the optimizer used the index in all cases except one: when both columns were null and I did not include the sentence NOT IN
:
SQL> desc big_table
Name Null? Type
----------------------------------- ------ -------------------
ID NUMBER
COL1 NUMBER
COL2 VARCHAR2(30 CHAR)
COL3 DATE
COL4 NUMBER
No NOT IN clause ...
SQL> explain plan for
2 select col4, count(col1) from big_table
3 group by col4
4 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1753714399
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31964 | 280K| | 7574 (2)| 00:01:31 |
| 1 | HASH GROUP BY | | 31964 | 280K| 45M| 7574 (2)| 00:01:31 |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 2340K| 20M| | 4284 (1)| 00:00:52 |
----------------------------------------------------------------------------------------
9 rows selected.
SQL>
When I took the offer again NOT IN
, the optimizer decided to use an index. Weird.
SQL> explain plan for
2 select col4, count(col1) from big_table
3 where col1 not in (12, 19)
4 group by col4
5 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 343952376
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31964 | 280K| | 5057 (3)| 00:01:01 |
| 1 | HASH GROUP BY | | 31964 | 280K| 45M| 5057 (3)| 00:01:01 |
|* 2 | INDEX FAST FULL SCAN| BIG_I2 | 2340K| 20M| | 1767 (2)| 00:00:22 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
2 - filter("COL1"<>12 AND "COL1"<>19)
14 rows selected.
SQL>
To reiterate, in all other cases, if one of the indexed columns was declared non-nill, the index was used to satisfy the request. This may not be true in earlier versions of Oracle, but it probably points the way forward.
a source to share
you can use the hint http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm , but remember that using an index may not always lead to faster execution.
a source to share
You are querying oracle fixed tables, since you didn't specify which db vesion it is, I'll take the last one. Have the fixed tables analyzed and the statistics updated? Have you tried your query with the Rule Base Optimizer using the / * + rule * / hint rule. I have often seen queries against oracle native fixed tables perform better when using the Rule Base Optimizer.
a source to share