Tagged Questions
1
vote
2answers
280 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
2
votes
1answer
41 views
Convert Log table days in a state
This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following...
Input Table
Date Item Moved to Box
Oct-1 ...
1
vote
1answer
90 views
What is the best relationship model for high performance?
I want to model a relationship between Customer and Transaction.
However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
3
votes
3answers
199 views
optimizing a compare query
Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
2
votes
2answers
2k views
forcing Oracle to use hash join for a subquery
I have a query that looks like
SELECT *
FROM table0
WHERE id IN (SELECT id FROM table1 JOIN table2)
Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and ...
0
votes
0answers
91 views
Optimization of an Oracle query
I have a view which is accessed via DBLink in Oracle. This view is made up of different tables. Now we have to execute queries on this view. Example:
Select * from Viewname@dblink where ...
3
votes
1answer
230 views
Measuring the relative performance of queries given the assertion that “cost” isn't reliable
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.
Source
Does ...
0
votes
0answers
671 views
Query alternates between fast and slow execution
We have an Oracle 11g2 installation. I have a query which joins four tables of approx. 30,000 rows each. The join conditions mostly are simple equalities. There are some NOT LIKE on strings and some ...
3
votes
1answer
132 views
How to search for an ID and its descendants in this query?
I have the following tables:
Project Unit
------------- ---------------
ID | IDUnit ID | IdParent
I have to select all projects that have the IDUnit ...
1
vote
1answer
586 views
At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?
I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.
I guess that it ...
4
votes
1answer
816 views
How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:
FOR COLUMNS SIZE 5 some_column
and
FOR COLUMNS some_column SIZE 5
both will create a ...
3
votes
2answers
936 views
DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle
In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table ...
