2

I have table (2139868 rows):

COLUMN1 NUMBER(20,0)
COLUMN2 XMLTYPE
COLUMN3 XMLTYPE
COLUMN4 VARCHAR2(50 BYTE)
COLUMN5 VARCHAR2(50 BYTE)
COLUMN6 VARCHAR2(50 BYTE)
COLUMN7 VARCHAR2(50 BYTE)
COLUMN8 VARCHAR2(50 BYTE)
COLUMN9 VARCHAR2(500 BYTE)
COLUMN10 VARCHAR2(100 BYTE)
COLUMN11 VARCHAR2(50 BYTE)
COLUMN12 VARCHAR2(20 BYTE)
COLUMN13 VARCHAR2(20 BYTE)
COLUMN14 TIMESTAMP(6)
COLUMN15 VARCHAR2(50 BYTE)
COLUMN16 PROPERTIES_ARRAY_TYPE
COLUMN17 TIMESTAMP(6)
COLUMN18 TIMESTAMP(6)

In a database

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I'm trying to select all the distinct values of COLUMN4; unfortunately this takes roughly 18 seconds despite the COLUMN4 being indexed. There are only 5 unique items.

How can I speed up my query?

(Work doesn't let me upload images so I'll type out the execution plan, use your imagination)

Operation             OBJECT_NAME CARDINALITY   COST
---------------------- ----------- ----------- ------
SELECT STATEMENT       NIL                    4 65463
+ HASH(UNIQUE)         NIL                    4 65463
++ TABLE ACCESS (FULL) TABLE1           2010857 65334
Improve this question
6
  • Is the column nullable? And what does the execution plan say?
    – a_horse_with_no_name
    Apr 17, 2014 at 21:57
  • Yes, 1 of the 5 items is a null. Please see the ugly update.
    – user37250
    Apr 17, 2014 at 22:04
  • Well if the column is nullable the index cannot be used as tuples consisting completely of null values won't make it into the index. You can trick Oracle to index them nonetheless using an index defined as (column4, 1). Btw: why don't you just copy and paste the text result of an explain. That is usually much easier to read than an image or some other obfuscated output.
    – a_horse_with_no_name
    Apr 17, 2014 at 22:07
  • I'm not sure how to do so. If I use docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm#838 then the plan_table is a few thousand characters wide.
    – user37250
    Apr 17, 2014 at 22:11
  • explain plan for select ...; select * from table(dbms_xplan.display);
    – a_horse_with_no_name
    Apr 17, 2014 at 22:13

2 Answers 2

Reset to default
9

If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table. Rather than trying to tune your current query, I would bet that the better answer would be to create a separate table that has just those 5 distinct values, create a foreign key relationship between your current table and the new lookup table, and then modify your query to hit the new lookup table instead.

Improve this answer
5
  • But does this help to solve the original problem? Finding the distinct values used in the table will need a full tablescan anyway.
    – miracle173
    Apr 18, 2014 at 7:26
  • 1
    It does @miracle173, by fixing the data-model you make the database work for you rather than having to change the structure of the database. It also means that if you massively increase the number of rows in the table you're not going to have another speed issue.
    – Ben
    Apr 18, 2014 at 13:00
  • @Ben: Sorry, I don't understand your answer. Changing the data model and adding a lookup table is a change to the database structure but will not speed up te original query. Maybe Justin Cave is right when he says that the users want to know the list of possible values for this columns and so they could query the lookup table. But this is only specultion
    – miracle173
    Apr 19, 2014 at 20:16
  • If there's a foreign key it does speed up the query @miracle, there's no need to query the original query; you can query a 5 row table instead. If this table exists you don't need to play around with different indexes, IOTs, ordered indexes, moving tablespaces onto different disks etc., you just query the 5 rows...
    – Ben
    Apr 19, 2014 at 22:31
  • OP here. Unfortunately that crossed my mind but the table is transactional data and as such it's "designed" to be self contained. Also my company hates maintaining relations.
    – Colton
    Apr 22, 2014 at 16:14
6

If Oracle only scans the index it cannot decide if there is a row with NULL in this column. So it makes a full table scan. A possible workaround: create a bitmap index on column4. This index contains the NULL columns, too. More details can be found in this post Why isn't oracle using an index for distinct query ?

Improve this answer
2
  • +1 The bitmap index for a small set of values is the way to go.
    – Alexandros
    Apr 18, 2014 at 11:46
  • In a comment below, the original poster indicates that this is a "transactional" table. If that means that it is involved in OLTP operations, a bitmap index may not be appropriate both from a locking standpoint and from a growth standpoint.
    – Justin Cave
    Apr 22, 2014 at 16:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.