All Questions
97
questions
0
votes
2
answers
365
views
Oracle SQL Performance issue with 'IS NULL' - NVL
I am using Oracle 12.2 version. I have SQL that has two filter condition to fetch NULL values from huge table. i have converted that IS NULL to NVL (OFF_CODE, -1)=-1 and nvl(off_date,'01-JAN-1900')= '...
- 39
0
votes
1
answer
810
views
NOT Equal(<>) in Oracle SQL is ignoring Index and NULL valued rows
I have one query where need to filter based on NOT Equal values in Oracle SQL. Can not use EQUAL to condition as the possible values fo the column in unknown.
Query:
SELECT * from employee where name &...
1
vote
1
answer
106
views
Why does Oracle execute both TABLE SCAN and INDEX UNIQUE SCAN on the same table in one query?
I am not looking for a optimization of the following, just an explanation. I have this query:
SELECT COUNT(LARGE_A.id_a), SUM(LARGE_A.b_integer)
FROM LARGE_A
INNER JOIN MEDIUM_A ON LARGE_A.id_a = ...
- 2,011
0
votes
0
answers
56
views
Almost equal table with different running time
I’m using oracle. I have two table A and B
Table A has 8000 rows and 5 five columns
Table B has 5500 rows and same 5 columns
All of 5500 rows in table B are contained in Table A and they are the same
...
- 1
0
votes
1
answer
354
views
Oracle not using my bitmap indexes in my query
The test_data table I am using in my query can be generated as per the below code:
DROP TABLE test_data;
CREATE TABLE test_data (
random_value NUMBER,
random_string VARCHAR2(20),
...
- 784
0
votes
1
answer
681
views
Explain plan hint details as plain text
I'm trying to learn how to force Oracle 18c to use a function-based index — by using a hint:
--I've omitted the custom function and function-based index because it cluttered the question too much.
--...
- 398
2
votes
1
answer
999
views
oracle json_value vs. json_table explain query plan
I have a query that it's been written a while ago. Basically a Materialized View that uses json_table function.
Recently since we moved to Oracle 19c that MV sometimes works and other times doesn't.
I ...
- 359
0
votes
1
answer
217
views
Optimize a query with IN() clauses oracle
Here I have a query like below:
SELECT /*+ USE_NL_WITH_INDEX(D) */ N.MARCHE,N.NLBIX,D.NUORD,D.NUCON, 0 NUBI2, N.COINF, D.COINL, D.COINA, D.COINV, N.COINN, D.COINC, D.NUCPT, D.COINI, N.COINK, N.CNACT, ...
- 1
2
votes
1
answer
45
views
Statistics rapidly changing within transaction - fixing execution plan
A problem I'm facing (Oracle 11g):
I create a table, let's call it table_xyz, with index (not unique, no primary key).
I create package with procedure that will insert let's say 10 millions ofrecords ...
- 954
0
votes
2
answers
189
views
Read execution plan in Oracle
my question is why there is the full access in the option however I create two index for LIGPOR and DATRAI tables ??
and if there is any one can help me to clearly read the long execution plan in sql ...
- 1
0
votes
1
answer
356
views
Oracle filter by max date
I'am searching for the best way to filter a table by a MAX date.
Is it better to find with a select in the where
SELECT *
FROM
table1 foo
WHERE
foo.date_tmp =
(
...
- 115
0
votes
2
answers
167
views
Oracle DDL statment ( like CTAS) , after executed, not show in V$SQL
Why Oracle "DDL" statements (like "CTAS"), after executed, does not shown in V$SQL view ?
How can get "SQL_ID" of that? I want to use "SQL_ID" in SQl plan ...
- 57
1
vote
2
answers
1k
views
Improving the query processing performance of SUM and JOIN SQL
SELECT SUM(C_QUANTITY)
FROM CARS JOIN ORDERS
ON C_ORDERKEY = O_ORDERKEY;
I have this query that aggregate sum of L_QUANTITY from the JOIN tables. The query cost, by using EXPLAIN PLAN is 12147. The ...
- 47
2
votes
2
answers
690
views
How do I obtain a Query Execution Plan in Oracle IDEs?
In Oracle IDEs, such as Navigator, Toad, Oracle SqlDeveloper how can I get a query execution plan for a query / stored procedure?
I can find some commands for them online, even though I am not able to ...
- 123
0
votes
1
answer
44
views
Why does the optimizer decide to self-join a table?
I'm analyzing my query that looks like this:
WITH Project_UnfinishedCount AS (
SELECT P.Title AS title, COUNT(T.ID) AS cnt
FROM PROJECT P LEFT JOIN TASK T on P.ID = T.ID_PROJECT AND T....
- 125