Stack Overflow
Stack Overflow

Questions tagged [sql-tuning]

SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.

328 questions
2
votes
3 answers

How to improve NOT EXISTS performance in Oracle

INSERT INTO table3 SELECT tbl1.pk_1, tbl1.pk_2, tbl2.pk_3, tbl1.pk4 FROM table1 tbl1, table2 tbl2 WHERE tbl1.pk_1 = 'root' and NOT EXISTS (SELECT 1 FROM table3 tbl3 WHERE tbl3.pk_1 = tbl1.pk_1 and tbl3.pk_2 = tbl1.pk_2 and…
eeandrew
  • 89
  • 2
  • 9
2
votes
5 answers

Tuning SQL Select

I don't know too much about optimized SQL Selects and my query is very slow. Maybe you have some hints that makes my query faster. SQL Query SELECT DISTINCT CLI.FANTASIA AS Cliente , DBSMP.VEICULO_PLACA AS Placa , …
guisantogui
  • 4,017
  • 9
  • 49
  • 93
2
votes
2 answers

Data Calculations MySQL vs Python

I'm trying to understand which of the following is a better option: Data calculation using Python from the output of a MySQL query. Perform the calculations in the query itself. For example, the query returns 20 rows with 10 columns. In Python, I…
Ravi
  • 2,472
  • 3
  • 20
  • 26
2
votes
2 answers

SQL query performance with the joins on multiple tables

When we join more than 2-3 tables in a query, and if we have a common column in all the tables, will there be any difference in the performace when we specify the value to the common columns in all the tables. for ex: select e.* from emp e, dept…
Savitha
  • 405
  • 4
  • 15
  • 25
2
votes
2 answers

Oracle join multiple rows

I've two tables, one contains ID <-> name mapping and the other table contains more than one ID column. To list out the records of the second table with the corresponding names of the IDs, I've a query like SELECT (SELECT NAME FROM TAB2 WHERE ID =…
Rnet
  • 4,796
  • 9
  • 47
  • 83
2
votes
1 answer

Oracle OWB Cube load SQL tuning

I have a OWB mapping which takes input from a staging table and add those row to the Cube. The underlying table behind cube is a relational fact table joined with the dimensions using foreign keys. Explain plan behind the query has a rather high…
onlinedev
  • 21
  • 1
1
vote
1 answer

Is there a backend table like plan_table?

Is there a backend table like plan_table where we can see the output of autotrace, similar to explain plan output? Like if I am running a long query with autotrace and by chance, the session gets logged off while reading the autotrace output and I…
general46
  • 800
  • 6
  • 16
1
vote
1 answer

Unique index performance with or without unique column

Is there any difference on performance when an unique-indexed column has also a unique constraint on the related column? I know that unique indexes and non-unique indexes have differences on performance. But my question is, will there be any…
oramas
  • 881
  • 7
  • 12
1
vote
2 answers

Does Oracle go for hard parse again if I truncate the table?

If I delete most of the rows in the table, or if I truncate it, does it go for hard parsing again, even if it was soft parsing before? I am confused because the statistics will change but the query doesn't change. Besides, does it go for hard…
oramas
  • 881
  • 7
  • 12
1
vote
1 answer

Need suggestion for POSTGRES Tuning

I am in a need to run the few complicated queries frequently on my database, one of my queries is taking around 57 sec to execute, I am trying a way to tune my Postgres database, so, I can cut the execution time to half or more than that. I have…
Sujeet Chaurasia
  • 97
  • 8
1
vote
1 answer

Why Oracle Doesn't Use Index Full Scan on Non-Unique Indexes?

I created a copy of the employees table in the HR schema. Then added a non-unique index on the employee_id column. create table employees1 as select * from employees; create index temp_idx on employees1(employee_id); Then checked its execution…
oramas
  • 881
  • 7
  • 12
1
vote
1 answer

How to solve memory problems using oracle collections?

I recently faced with an interview question which is: I have very little memory available in my session and I'm using a collection to store the entire records fetched by a cursor. Due to low memory, my session crashed. How will I handle this and…
oramas
  • 881
  • 7
  • 12
1
vote
1 answer

Index scan is not working on json data set in postgres

I'm trying to understanding of extracting data from JSON files in PostgreSQL with certain filter conditions. Here is my query, created index like below, CREATE INDEX idx_startTimeL_n ON mytable USING btree (((data -> 'info'::text) ->>…
Rj_N
  • 192
  • 1
  • 10
1
vote
1 answer

Does Oracle database performs sort in Buffer Cache or PGA?

As far as I know, Oracle performs the sort operations in PGA. But does it also do that in Buffer Cache or Shared Pool etc? If so, in which conditions does it sort in these areas?
oramas
  • 881
  • 7
  • 12
1
vote
1 answer

Does redo logs store all the changes applied to database buffer cache?

I know that the redo log entries are created when there is insert/update/delete/create/drop/alter occurs. What information gets stored in redo log ? In case of instance failure, redo log file is used to recover database, does it contain information…
general46
  • 800
  • 6
  • 16
1 2 3
…
21 22