AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 1 hour 35 min ago

PL/SQL Performance Tuning

Thu, 04/12/2018 - 00:00
My stored PROCEDURE searches for potential duplicate PeopleSoft Vouchers (PS_VOUCHER, PS_VOUCHER_LINE, PS_DISTRIB_LINE, PS_VENDOR, PS_PYMNT_VCHR_XREF). The first step is to get the set of unique concatenated/delimited values for records that are cur...

Move subpartition to another partition

Thu, 04/12/2018 - 00:00
Hi. Today I've discovered another issue on the DB I've inherited. I've found this range subpartitions in wrong partitions. Let me try to explain. The scenario is: Partitions with month values subpartitioned by weeks, so I expect to have a ...

Is safe to use row_number() over (order by null) as a rownum alternative?

Fri, 03/23/2018 - 00:00
Hi, I did some testing and it seems that row_number() over (order by null) could be used as an alternative to oracle's rownum pseudocolumn. Is this behavior by design or is just a coincidence? I'm trying this because some queries are very slow...

How to Optimize design a 1000+ columns in a Oracle DB

Tue, 03/20/2018 - 00:00
Hi, We need to store 5 minute interval data, with additional attributes, which will be around 1000+ columns. How to design this optimally for a Fact Table? Doe we have any columnar design in Oracle, such as a vertical split? Please provide your ex...

using max function

Sun, 03/18/2018 - 00:00
Hi, Look at here..... Find highest salary in each department without using MAX function. Note:- Use a single SELECT statement only. For an added complexity (optional): try not using ANY functions a...

Datapump SQL minitoring with SQL (not PL/SQL)

Thu, 03/01/2018 - 00:00
I'm on 11.2 and 12.1 and would like to monitor datapump sessions with pure SQL (I am not allowed to create objects on the databases). I can monitor using: <code> select ss.sid , s.job_name , j.state --, j.job_mode --, j.degree , dbms_rcvm...

Foreign key constraint DDL & locks

Wed, 02/28/2018 - 00:00
For the period when DDL runs to add FK constraint (on un-indexedcolumn), both child and parent tables are unavailable to be read by 2nd session. Is this behaviour expected / documented? Is there anyway to avoid issue (which manifests in applicati...

database performance select count

Fri, 02/23/2018 - 00:00
Hi Team , i have query mentioned below : T1(id number , name varchar2 (20)); Column ID is full of null in table T1. select count(*) from table t1 where id is not null and name like :b1; This query is going on FTS , so we rewritten i...

Checking the checksum during purging process

Thu, 02/22/2018 - 00:00
Hi, We are developing an Archiving mechanism in which there is a procedure defined in the package that achieves the old partitions(according some business logic). We don't purge the partition as soon as it gets archived. We truncate the partition af...

Using SELECT * combined with WITH-CLAUSE - Bad Practice? View gets compiled with static columns list

Tue, 02/20/2018 - 00:00
Hey guys, I have a question regarding clean SQL Code / Bad Practice around the use of wildcards in SELECT-Statements. In the provided example I have a base-query with a huge list of columns selected and two (or more) sources I need to have combin...

ORA-01502 with hardcoded index hints

Mon, 02/19/2018 - 00:00
We have EBS 12.1.3, which has several indexes, that we disable for bulk loading at night and rebuild them once done. While they are disabled some queries get ORA-01502 because of hard coded index hint on these unusable indexes, although we have skip...

Update current row witrh values from previous row

Thu, 02/15/2018 - 00:00
Hi, I'm searching for a solution to solve update with a single SQL statement instead of a PL/SQL procedure: <code>create table test (stock_date DATE, stock NUMBER(5), stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5)); INSERT INTO tes...

SUBPARTITION ELIMINATION

Wed, 02/14/2018 - 00:00
Table with 10 years worth of data Has two key date columns. Queries are always by one of the dates. <b>But never both</b>. So how to get partition elimination to work for both when really can only partition by 1 column. **Thought was - since ...

How to Improve the Performance of Group By with Having

Wed, 02/14/2018 - 00:00
I have a table t containing three fields accountno, tran_date, amount and amount is always > 0. . There are many records for each accountno. I want to select all the accountnos where the sum(amount) > 100. The simple query is like this <code>...

Select data without duplicate

Wed, 02/14/2018 - 00:00
Right now data like this, Case# Unit# ------------------------ X0002270301 null X0002460601 T0707802116 X0002460601 null X0002683101 null x0002683101 T0721128616 x0002922501 null x0005125201 null x0005125201 T0813774816 ...

Using defined variable (ampersand) for a part of column name

Sun, 02/11/2018 - 00:00
Good day. I am having problem to define <b><u>only part</u></b> of a column name as a variable (using ampersand). Script example: <b><i>Select &Product._ID, &Product._NAME, &Product._SALES, &Product._DEBT From TABL...

Using SYS_GUID() has primary key in large table

Thu, 02/08/2018 - 00:00
Hey, I have a large table 600 million rows, approximately 70gb, not partitioned (yet), a few indices, etc. I have reports that query this table often but they're very slow (20-60 minutes at times). I want to create this materialized view but ...

Materialized views and Synonyms

Mon, 02/05/2018 - 00:00
Good day. I need help with refresh materialized view. I created synonym MySynonym: <code>CREATE OR REPLACE SYNONYM "MyScheme"."MySynonym" FOR "MyScheme2"."SomeTable";</code> I created materialized view: <code>CREATE MATERIALIZED VIEW MyMView T...

SQL request with analytics to fill previous values

Thu, 02/01/2018 - 00:00
Hello, I have a table with this structure table values: <code>(code varchar2(10), date_value date, value number);</code> Example data : <code>'Code1','15/03/2017',25000 'Code1','06/06/2017',26000 'Code1','18/07/2017',29000 'Code1...

No estimate time remainings drop column unused, with checkpoint.

Wed, 01/31/2018 - 00:00
Hi Tom! I had a problem removing a column set unused(version RDBMS 11.2.0.4) Because he was using CHECKPOINT, could not keep track of a running development, as well as estimate in v$SESSION_LONGOPS. For time remaings estimate, I was using an ave...

Pages