SQL optimization using LISTAGG – Avoid repeat self join on child table.

I have scenario where there is OBJECTS table and it has child table ATTRIBUTES. The each row in OBJECTS can have different sets (ie different count, and types) of ATTRIBUTES.  (To make it easy to understand, i keep the tables simple).

CREATE TABLE objects
(
id INT,
name VARCHAR2(256),
PRIMARY KEY (id)
);

CREATE TABLE attributes
(
id INT,
object_id INT,
name VARCHAR2(256),
value VARCHAR2(256),
PRIMARY KEY (id),
CONSTRAINT fk_order FOREIGN KEY (object_id) REFERENCES objects(id)
);

INSERT into objects values(1, ‘toy’);
INSERT into attributes values(1, 1, ‘color’, ‘blue’);
INSERT into attributes values(2, 1, ‘weight’, ‘2lb’);
INSERT into attributes values(3, 1, ‘price’, ‘$25’);

 

INSERT into objects values(1, ‘food’);
INSERT into attributes values(2, 1, ‘weight’, ‘2lb’);
INSERT into attributes values(3, 1, ‘price’, ‘$25’);

Now we want list each OBJECT and corresponding attribute like.

ID NAME COLOR WEIGHT PRICE
1 toy blue 2lb $25

SELECT
o.id id,
o.name name,
a1.value color,
a2.value weight,
a3.value price
FROM
objects o,
attributes a1,
attributes a2,
attributes a3
WHERE
(o.id = a1.object_id and a1.name=’color’)
AND        (o.id = a2.object_id and a2.name=’weight’)
AND       (o.id = a3.object_id and a3.name =’price’)

This worked and gave us the required output. When i looked at this in code review i had some concerns with this approach. The ATTRIBUTES table is self joined 3 time and if  developer needed more attributes then he\she would have to rejoin ATTRIBUTES table that many more times. In production attribute table would have thousands of rows, this will turn into performance issue.

If we had this data in de-normalized form we would not have run into this issue. De-normalizing 2 tables is minor change, but in actual scenario it was connected to lot of other data, the de-normalization would lead to cascading effort. So i approached our DBA to pick their brains on this. He suggest use of LISTAGG, and with his help optimized query looked like below. 

select object_attr.id,
object_attr.name
LISTAGG(object_attr.value, ‘|—-|’) WITHIN GROUP (ORDER BY object_attr.attr_name) attribute
from (select o.id, o.name, a.name attr_name, a.value
from objects o,
attributes a
where a.name in (‘color’,’weight’,’price’)
and o.id = a.object_id
) object_attr
GROUP BY object_attr.id, object_attr.name
ORDER BY object_attr.id

ID NAME ATTRIBUTE
1 toy blue|—-|$25|—-|2lb

Explain plan for self joining un-optimized query was

Operation                            Node Cost             Cost CPU           Cost I/O         Cost Optimizer            Cardinality        Bytes Position          Partition Start       Partition Stop              Partition Id
SELECT STATEMENT       0.0 %                      1160                   107,161,454          1150                           ALL_ROWS                  39                            34671                1160

Explain plan for optimized query was

Operation                           Node Cost              Cost CPU             Cost I/O         Cost Optimizer             Cardinality          Bytes Position         Partition Start         Partition Stop        Partition Id
SELECT STATEMENT       0.0 %                     391                       47,603,589               387                            ALL_ROWS                193                              61374                        391

As always, with time we updated the query to get more attributes from ATTRIBUTES Table. The un-optimized query’s CPU and IO cost would have gone up exponentially with each new attribute fetch, but in our optimized query cost was pretty much constant. We had to do little more to parse the attribute field in java.

Debug SSL issue – part 2 (2 way SSL)

I covered 1 way SSL debug in – Debug SSL issue – part 1 (1 way SSL)

The only difference with 2 way SSL is additional step to verify the client certificate.

  • In *** ServerHello, TLSv1.2, the server challenges client to provide its certificate as well, you see below at the end of the server hello

*** CertificateRequest
Cert Types: RSA, DSS
Cert Authorities:
<CN=XXXXXXXXXXxXXXXX>
<OU=XXXXXXXXXXXXXXX>
<CN=XXXXXXXXXXXXXXXx>

  • Basically server is asking client provide a certificate that signed by any of the certificate authority (CA) provided in the list. Server only trust these CAs.
  • Client does look up in keystore \ identity store to find cert that match the list above. If it find one, it sends that cert to server.
  • Server that validates the cert sent by client, if it find that cert or cert chain in trust store, it prints

matching alias: XXXXXX
*** Certificate chain
chain [0] = […………

  • The next steps are similar to 1 way SSL.

 

 

Debug SSL issue – part 1 (1 way SSL)

In java to debug SSL issue, add -Djavax.net.debug=ssl to java command line argument. This can be added either to server side or client side and will print the SSL handshake details between the client server on standard out.

I assuming this 1 way SSL connection from client to server. For details on 1 way SSL – https://blogshri.wordpress.com/2014/08/24/ssl-part-3-https-communication-type/

Caution

  1. SSL debug is every verbose and prints a lot in log, so if you doing debug on the server side, it may be good to limit communication to server from client you are facing issue with.
  2. The exact output you may see may vary based on version of Java Runtime (JRE), i will try to keep this discuss generic.
  3. In enterprise environment there are lot of component between client and server – proxy, firewall, load balancer etc, so first debug it to make your request from client is making to server. Look at SSL debug only once the you have establish the communication from client is reaching server. And its failing in SSL handshake between client and server.

Once you have this added, let me explain how to understand the details that are printed.

  • The first thing it will print is *** ClientHello, TLSv1.2, this indicate the client request is making it to server and the connection protocol is TLSv1.2. After this line it will print bunch of details like cipher suite, extension that client accepts, rejects etc.
  • The server reciprocates with server hello *** ServerHello, TLSv1.2, this indicate the server is received the request and protocol that it will use. Also after it indicates the cipher it has selected. If there are cipher suite in common, you will see the error – no cipher suites in common.

Then as part of server hello, server presents it certificate

RandomCookie: GMT: 1495682071 bytes = { 188, 152, 123, 228, 111, 6, 216, 173, 128, 2,
chain [0] = [
[
Version: V3
Subject: XXXXXXXXXXXXXXXXXXXXXXXXXX
Signature Algorithm: SHA1withRSA, OID = 1.2.840.113549.1.1.5

Key: Sun RSA public key, 2048 bits
modulus: XXXXXXXXXXXXXXXXXXXxxxx
public exponent: 65537
Validity: [From: Thu May 12 14:47:31 CDT 2016,
To: Wed May 13 14:47:31 CDT 2026]
Issuer: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SerialNumber: [ c8]

  • And prints *** ServerHelloDone.
  • Now the client has to validate this certificate against the cert that present in its trust store. So client side you should see something like

***
Found trusted certificate:
[
[

If you dont have right certificate or CA in trust store in client side or if trust store if not correctly configured,  you will see error at this point in the log like – unable to find valid certification path

  • If certificate is found in client key store, client server exchange the symmetric key (based on cipher suite used) that will be used for any further communication.