sql query requires tuning

[moin73]

Following query is working fine individual, however if I joined this query with view (v_company_alias) system & performance goes down.

How I can optimize this query, so I can achieve best performance, please advise me.

Here is detail

Thanks


SELECT DISTINCT company.company_id, company.company_name, company_status
FROM company ,v_company_alias
WHERE UPPER(v_company_alias.company_name) LIKE 'L%'
AND company.company_id = v_company_alias.company_id
ORDER BY company_status,company_name

View : v_company_alias

CREATE NOFORCE VIEW COMPANY_ALIAS
(COMPANY_ID,COMPANY_NAME)
as
SELECT company_id,
alias company_name
FROM company_alias
WHERE deactivated_indicator = 'F'
UNION
SELECT company_id,
company_name
FROM company WITH READ ONLY

INDEXES : company.company_id

company_alias.alias_id

company_alias.company_id (FK)


1, company
Name Null? Type
------------------- ----------------------------
COMPANY_ID NOT NULL VARCHAR2(20)
SOURCE_TYPE_ID NOT NULL VARCHAR2(20)
COMPANY_NAME NOT NULL VARCHAR2(100)
TEMPORARY_NAME_INDICATOR VARCHAR2(1)
COMPANY_STATUS VARCHAR2(50)
DEACTIVATED_INDICATOR NOT NULL VARCHAR2(1)

2, company_alias
Name Null? Type
-----------------------------------------------
COMPANY_ALIAS_ID NOT NULL NUMBER(10)
ALIAS NOT NULL VARCHAR2(100) COMPANY_ID NOT NULL VARCHAR2(20)
INSERTED_TIMESTAMP NOT NULL TIMESTAMP(6)
DEACTIVATED_INDICATOR NOT NULL VARCHAR2(1)

3, v_company_alias
Name Null? Type
------------------- ----------------------------
COMPANY_ID VARCHAR2(20)
COMPANY_NAME VARCHAR2(100)

 

[SantaMufasa]

Moin,

Please advise the composition of indexes on the underlying tables.

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.

 

[moin73]

I did not get it. Please clarify in detail.

 

[SantaMufasa]

I mean, please run this query and post the results:

break on a on d
col a heading "Table|Name" format a15
col b heading "Col|Pos|in|Index" format 9
col c heading "Column|Name" format a20
col d heading "Index|Name" format a30
select table_name a, index_name d, column_position b, column_name c
from user_ind_columns
where table_name in ('COMPANY','COMPANY_ALIAS')
order by a, d, b
/

My concern about the indexes results from your list of indexed columns: you list "company_alias.alias_id" as an indexed column, but the "company_alias" table has no column named "alias_id".

Also, I believe your posting of your view definition is incorrect: your code reads, "CREATE NOFORCE VIEW COMPANY_ALIAS..." I believe you intended "V_COMPANY_ALIAS". Is my assessment correct?

Recommendation(s): I recommend the creation of the following indexes to improve performance:

CREATE INDEX company_alias_name
ON company_alias(UPPER(alias));

CREATE INDEX company_alias_deact_ind
ON company_alias(deactivated_indicator);

Please post the query results and confirm that the posted code for your view definition was incorrect.

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.

 

[moin73]

Thanks,

You are right that was typo in my initial posted.

I will post a result of my query.

Regards