Ashish sinhaMay 16, 20205 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 5, 20206 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 sinhaDec 3, 20202 minBitmap Conversion From Rowid Several times while checking the explain Plan, I find bitmap conversion to rowid which could slow down the performance of the query. The...
Ashish sinhaNov 7, 20203 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 1, 20202 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 30, 20202 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 28, 20203 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 17, 20203 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 4, 20204 minFull Table Scan in OracleThere 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 27, 20202 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 19, 20202 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 7, 20202 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 1, 20203 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 23, 20202 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 10, 20203 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 1, 20203 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 5, 20202 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 10, 20206 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 20, 20201 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 19, 20202 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 ...