There is a unique index case_id(case_id,address_id,company_id) that
needs to be used.
Each case_id is actually unique.
as soon as i drop the
SELECT DISTINCT associated_address.case_id
FROM associated_address, address
WHERE (address.company_id= associated_address.company_id
AND address.address_id =associated_address.address_id
AND address.company_id = 'SYS'
ANDaddress.last_name = 'Plaugher'
AND address.postal_code = '45804' ))
piece out of the query it returns sub second.
SQL> desc associated_address;
Name Null? Type
- -------- ------------
COMPANY_ID NOT NULL CHAR(3)
CASE_ID NOT NULL NUMBER(8)
ADDRESS_ID NOT NULL NUMBER(8)
DISPLAY_SEQUENCE NUMBER(6)
ISSUE_SEQ NUMBER(3)
DESCRIPTION VARCHAR2(40)
SQL> desc case_id
Name Null? Type
- -------- ------------
COMPANY_ID NOT NULL CHAR(3)
CASE_ID NOT NULL NUMBER(8)
INITIAL_USER_CODE VARCHAR2(40)
RESPONSIBLE_USER_ VARCHAR2(40)
CODE
ADDRESS_ID NOT NULL NUMBER(8)
CASE_STATUS CHAR(1)
CASE_STATUS_CODE VARCHAR2(40)
RECEIVED DATE
CLOSED DATE
TOTAL_WORK_TIME NUMBER(8)
CONTACT_MULTIPLIE NUMBER(3)
R
INCOMING_PHONE VARCHAR2(20)
B05_CODE VARCHAR2(40)
B06_CODE VARCHAR2(40)
B07_CODE VARCHAR2(40)
B08_CODE VARCHAR2(40)
B09_CODE VARCHAR2(40)
B10_CODE VARCHAR2(200
0)
B11_CODE VARCHAR2(40)
B12_CODE VARCHAR2(40)
B13_CODE VARCHAR2(40)
B14_CODE VARCHAR2(40)
B15_CODE VARCHAR2(40)
B16_CODE VARCHAR2(40)
B17_CODE VARCHAR2(40)
B18_CODE VARCHAR2(40)
B19_CODE VARCHAR2(40)
FIRST_PICK_DATE DATE
FIRST_PICK_QUEUE_ VARCHAR2(40)
USER_CODE
FIRST_PICK_USER_C VARCHAR2(40)
ODE
FIRST_PICK_TIME NUMBER(13,7)
QUEUE_TIME NUMBER(13,7)
QUEUE_COUNT NUMBER(6)
TRANSFER_COUNT NUMBER(6)
FIRST_RESPONSE_DA DATE
TE
FIRST_RESPONSE_TI NUMBER(13,7)
ME
FIRST_RESPONSE_US VARCHAR2(40)
ER_CODE
COMMUNICATION_COU NUMBER(6)
NT
CLOSED_USER_CODE VARCHAR2(40)
CLOSED_TIME NUMBER(13,7)
PCI_STATUS CHAR(1)
B20_CODE VARCHAR2(2000)
B21_CODE VARCHAR2(40)
B22_CODE VARCHAR2(40)
B23_CODE VARCHAR2(40)
B24_CODE VARCHAR2(40)
B25_CODE VARCHAR2(40)
B26_CODE VARCHAR2(40)
B27_CODE VARCHAR2(40)
B28_CODE VARCHAR2(40)
B29_CODE VARCHAR2(40)
B30_CODE VARCHAR2(40)
B31_CODE VARCHAR2(40)
B32_CODE VARCHAR2(40)
B33_CODE VARCHAR2(40)
B34_CODE VARCHAR2(40)
B35_CODE VARCHAR2(40)
B36_CODE VARCHAR2(40)
B37_CODE VARCHAR2(40)
B38_CODE VARCHAR2(40)
B39_CODE VARCHAR2(40)
B40_CODE VARCHAR2(40)
B41_CODE VARCHAR2(40)
B42_CODE VARCHAR2(40)
B43_CODE VARCHAR2(40)
B44_CODE VARCHAR2(40)
B45_CODE VARCHAR2(40)
B46_CODE VARCHAR2(40)
B47_CODE VARCHAR2(40)
B48_CODE VARCHAR2(40)
B49_CODE VARCHAR2(40)
SQL> desc address
Name Null? Type
- -------- ------------
COMPANY_ID NOT NULL CHAR(3)
ADDRESS_ID NOT NULL NUMBER(8)
ADDRESS_TYPE_CODE VARCHAR2(40)
ADDRESS_CODE VARCHAR2(40)
ACTIVE CHAR(1)
ACCOUNT_NUMBER VARCHAR2(40)
NAME_TITLE VARCHAR2(12)
GIVEN_NAMES VARCHAR2(25)
MIDDLE_INITIAL CHAR(1)
LAST_NAME VARCHAR2(25)
SUFFIX VARCHAR2(5)
COMPANY_NAME VARCHAR2(40)
JOB_TITLE VARCHAR2(40)
ADDRESS1 VARCHAR2(40)
ADDRESS2 VARCHAR2(40)
ADDRESS3 VARCHAR2(40)
CITY VARCHAR2(30)
STATE CHAR(3)
POSTAL_CODE VARCHAR2(10)
COUNTRY CHAR(3)
EMAIL VARCHAR2(40)
SEARCH_NAME VARCHAR2(40)
SEARCH_ADDRESS VARCHAR2(40)
ORIGINATED_VIA CHAR(1)
ORIGINATED_DATE DATE
LAST_MODIFIED DATE
ALLOW_SURVEY CHAR(1)
LAST_SURVEY DATE
LAST_CONTACT DATE
ACCUMULATED_GOODWILL NUMBER(12,2)
WHERE_TO_BUY CHAR(1)
LATITUDE NUMBER(9,6)
ONGITUDE NUMBER(9,6)
INSTRUCTIONS VARCHAR2(255)
A05_CODE VARCHAR2(40)
A06_CODE VARCHAR2(40)
A07_CODE VARCHAR2(40)
A08_CODE VARCHAR2(40)
A09_CODE VARCHAR2(40)
A10_CODE VARCHAR2(40)
A11_CODE VARCHAR2(40)
A12_CODE VARCHAR2(40)
A13_CODE VARCHAR2(40)
A14_CODE VARCHAR2(40)
A15_CODE VARCHAR2(40)
EMAIL2 VARCHAR2(255)
A16_CODE VARCHAR2(40)
A17_CODE VARCHAR2(40)
A18_CODE VARCHAR2(40)
A19_CODE VARCHAR2(40)
A20_CODE VARCHAR2(40)
A21_CODE VARCHAR2(40)
A22_CODE VARCHAR2(40)
A23_CODE VARCHAR2(40)
A24_CODE VARCHAR2(40)
A25_CODE VARCHAR2(40)
REPEATER_CODE VARCHAR2(40)
A26_CODE VARCHAR2(2000)
ENCL_AUTH_LEVEL NUMBER(2)
EMAIL3 VARCHAR2(255)
COUNTY VARCHAR2(40)
A27_CODE VARCHAR2(40)
A28_CODE VARCHAR2(40)
A29_CODE VARCHAR2(40)
A30_CODE VARCHAR2(40)
A31_CODE VARCHAR2(40)
A32_CODE VARCHAR2(40)
A33_CODE VARCHAR2(40)
A34_CODE VARCHAR2(40)
A35_CODE VARCHAR2(40)
A36_CODE VARCHAR2(40)
A37_CODE VARCHAR2(40)
A38_CODE VARCHAR2(40)
A39_CODE VARCHAR2(40)
A40_CODE VARCHAR2(40)
A41_CODE VARCHAR2(40)
A42_CODE VARCHAR2(40)
A43_CODE VARCHAR2(40)
A44_CODE VARCHAR2(40)
A45_CODE VARCHAR2(40)
A46_CODE VARCHAR2(40)
A47_CODE VARCHAR2(40)
A48_CODE VARCHAR2(40)
A49_CODE VARCHAR2(40)
A50_CODE VARCHAR2(40)
A51_CODE VARCHAR2(40)
A52_CODE VARCHAR2(40)
A53_CODE VARCHAR2(40)
A54_CODE VARCHAR2(40)
A55_CODE VARCHAR2(40)
A56_CODE VARCHAR2(40)
A57_CODE VARCHAR2(40)
A58_CODE VARCHAR2(40)
A59_CODE VARCHAR2(40)
A60_CODE VARCHAR2(40)
A61_CODE VARCHAR2(40)
A62_CODE VARCHAR2(40)
A63_CODE VARCHAR2(40)
A64_CODE VARCHAR2(40)
A65_CODE VARCHAR2(40)
A66_CODE VARCHAR2(40)
A67_CODE VARCHAR2(40)
A68_CODE VARCHAR2(40)
A69_CODE VARCHAR2(40)
A70_CODE VARCHAR2(40)
A71_CODE VARCHAR2(40)
A72_CODE VARCHAR2(40)
A73_CODE VARCHAR2(40)
A74_CODE VARCHAR2(40)
A75_CODE VARCHAR2(40)
A76_CODE VARCHAR2(40)
A77_CODE VARCHAR2(40)
A78_CODE VARCHAR2(40)
A79_CODE VARCHAR2(40)
A80_CODE VARCHAR2(40)
OPT_OUT CHAR(1)
LANGUAGE_ID VARCHAR2(40)
CURRENCY_CODE VARCHAR2(40)
PRIMARY_ADDRESS_ID NUMBER(8)
SQL>
On 7/15/05, amit poddar <amit.poddar_at_yale.edu> wrote:
> Could you also provide access and filter predicate information from the
> explain plan
>
> amit
>
> Chris Stephens wrote:
>
> >in spite of the odd responses, i'm still working on this if anyone has
> >any suggestions.
> >
> >i simply cannot get it to use the index without full scanning it.
> >...when i take the order by part out and hint it to use the index it
> >still full scans
> >
> >i broke the sql down:
> >
> >SQL> SELECT address.address_id
> > 2 FROM clorox_prod.address
> > 3 WHERE (address.company_id = 'SYS'
> > 4 AND address.last_name = 'Plaugher');
> >
> >A
> >
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 15 2005 - 08:49:17 CDT
