Table Design and Indexing

[THoey]

I should know the answer to this, but it has been too long since tuning class. I have a client that currently has a table similar to the following:

CALL_HISTORY

PHONE_NUM   VARCHAR2(10) NOT NULL
CUST_CODE   VARCHAR2(3)  NOT NULL
CALL_DATE   DATE         NOT NULL
...

Currently, there are two indexes, one on the CALL_DATE field, and another off the PHONE_NUM, CUST_CODE, and CALL_DATE.

Now, he is looking to alter the table. A CALL_HISTORY record may be tracked by either the actual phone number itself, or by the business phone number. Picture a large company with a lot of different phone numbers. It is probably one person's job to maintain the phone services, but there are hundreds of separate phone numbers. A record could be based off the specific phone number that called in, or the phone number of the person in charge of arranging all the phone service.

So, there will be another field added to each record:

CALL_HISTORY

PHONE_NUM     VARCHAR2(10) NULL
BUS_PHONE_NUM VARCHAR2(10) NULL
CUST_CODE     VARCHAR2(3)  NOT NULL
CALL_DATE     DATE         NOT NULL
...

Each record will have either the phone number, the business phone number, or both. He had intended to create a new index off the business phone number so that he had both phone numbers separately indexed.

The concern is when we query off this table, searching for values in either the phone number or the business phone number, that we may not use the indexes and then do a full table scan. Is there a way to design this so that it can be properly indexed to search in either one field or the other?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LKBrwnDBA]

What if BUS_PHONE_NUM is NULL?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[THoey]

Sorry, maybe I did not explain the question very well. I thought I mentioned that in any record there could be a phone number in just the PHONE_NUM field, in just the BUS_PHONE_NUM field, or in both the PHONE_NUM and BUS_PHONE_NUM fields.

Yes, there is the possibility that in any record either PHONE_NUM or BUS_PHONE_NUM could be null. According to the developers, they will make sure that at the very least one of those two fields is populated.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LKBrwnDBA]

You cannot index on a NULL column, I suggest you give the non-existent phone numbers a default value (perhaps 'n/a'?).

And create indexes for both columns.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

You cannot index on a NULL column...

Sure you can...Perhaps you mean that Oracle does not include in its indexes NULL entries.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[LKBrwnDBA]

Yes.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[THoey]

So, if Oracle does not index the null entries, that actually works to my clients benefit, as the indexes would only have records with a value in the key field.

If I am understanding this correctly, he could have the two indexes, one based off the PHONE_NUM and the other based off the BUS_PHONE_NUM, both indexes would also be based off the CALL_DATE. And then use a union query to get the results? Something like:

SELECT Distinct AO
FROM CALL_HISTORY
WHERE PHONE_NUM = sTn
AND CUST_CODE = sCustCode
AND NOT CALL_ID = sCallId
AND to_date(sysdate, 'dd-mm-yyyy') - to_date(call_date, 'dd-mm-yyyy') <= sDaysAgo
     UNION
SELECT Distinct AO
FROM CALL_HISTORY
WHERE BUS_PHONE_NUM = sBan
AND NOT CALL_ID = sCallId
AND to_date(sysdate, 'dd-mm-yyyy') - to_date(call_date, 'dd-mm-yyyy') <= sDaysAgo;

This would provide all the results where a record matched either the PHONE_NUM or the BUS_PHONE_NUM. And this would use the indexes efficiently as long as the indexes were made up of the PHONE_NUM or BUS_PHONE_NUM and the CALL_DATE?

Looking back at his suggested code, will the TO_DATE functions being used in the WHERE clause effect the index usage? I need to find out why he is using those anyways as the field is already of DATE type.

We are just trying to make sure this is efficient and not doing full table scans all the time

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Beilstwh]

The reason that they are using the to_date is to perform a trunc. What it is doing is an implicit conversion of the sysdate to a character string and then back to a date field. It is slow way to do it and one that will FAIL If the NLS_DATE_FORMAT ever changed. A MUCH faster and safer way would be to convert the string from

AND to_date(sysdate, 'dd-mm-yyyy') - to_date(call_date, 'dd-mm-yyyy') <= sDaysAgo;

to

AND trunc(sysdate) - trunc(call_date) <= sDaysAgo;

Bill
Oracle DBA/Developer
New York State, USA