Ashish sinhaMay 165 minWriting optimized PL/SQL code.PL/SQL was first released in 1992 as an optional extension to Oracle 6. In its initial years it lacked the optimizations needed to effi...
Ashish sinhaApr 56 minOracle JSONBeing a Relational DB developer for years it took a while for me to adapt to JASON- (JavaScript Object Notation) when Oracle introduced
Ashish sinhaNov 73 minINDEX SKIP SCAN(INDEX_SS) Prior to Oracle 9i version, if the leading column of an index wasn’t specified in a predicate, the index was effectively ignored by the C...
Ashish sinhaNov 12 minPARALLEL_INDEX HINTThe PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans, full ...
Ashish sinhaOct 302 minDBMS_UTILITY_FORMAT_ERROR_BACKTRACEOracle introduced this utility a while ago with 10g, but even now it's a hot topic in the interviews. I have witnessed this question quie...
Ashish sinhaOct 283 minFIRST_ROWS Hint in OracleI shared several post on performance recently. Whenever we talk bout performance first_rows can be of significant use. On many occas...
Ashish sinhaOct 173 minMaterialized View RefreshesInterviewers seldom miss to discuss about mv or snapshot and the refresh mechanism associated with it. While in OLTP environment, i did...
Ashish sinhaOct 44 minA delusion for Full Table Scan in Oracle.There is a common misbelief amongst us that FTS are evil, In one of my earlier blog i tried to give some insights on FTS and leverage a p...
Ashish sinhaSep 272 minFew Complex SQL query- asked as routine Questions in PL/SQL interviewsEight Years Back, i was naive developer, yearning to get a job into PL/SQL development, I faced multiple interviews. I still have some f...
Ashish sinhaSep 192 minCreate table for Exchange With clause on a Partitioned Table in Oracle Database 12c Release 2 (12.2)With the advent of ORACLE 12.2 the create table statement has been enriched by the new “for exchange with” clause. This enables us to cr...
Ashish sinhaSep 72 min XmlTable, extract and xmlupdate, XQuery In a NutshellEarly this week i was working on xml data in oracle, an Avenue still not much explored by me . I was trying to update an xml data from o...
Ashish sinhaSep 13 minLISTAGG DEDUPLICATION - A better ListAgg Function: Enhancement in Oracle 19CI was pondering since this weekend on what should I include in my blog until I found something exciting about the listagg, A built in ...
Ashish sinhaAug 232 minAlternatives to Performance Weary PL/SQL Function- (Result_cache, Deterministic Function)There are several real time problems where we come across Scanning of data records with PLSQL Functions, We prefer using functions for bu...
Ashish sinhaAug 103 minSql_injection & Bind VariablesThere are profound content scattered all over the internet which talks about proofing sql injection with bind variable as an option to s...
Ashish sinhaAug 13 minBind Variables: performance boosterOne fine morning, I got a request from my manager to drop some staging tables. I Started working on it and ended up quickly writing a dy...
Ashish sinhaJul 52 minRevealing the unseen secrets of IF clause.I hardly remember to have encountered with a subprograms without a conditional clause, are we not widely using the conditional structs i...
Ashish sinhaJun 106 minPerformance weary queries in oracleI had been going pity busy in recent time with several interviews scheduled. Every interview i took, Otherwise different in their questi...
Ashish sinhaMay 201 minPragma InlineInlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram. Pragma inline could ...
Ashish sinhaMay 192 minPRAGMA SERIALLY_REUSABLE IN ORACLEThe SERIALLY_REUSABLE Pragma specifies that the package state is needed for only one call to the server (for example, an OCI call to the ...
Ashish sinhaMay 92 minRelational to Oracle JSON This post is a continuation of my prior blogs in Oracle Jason, which gave us an insight on Oracle Jason - Storing Jason data and queryin...
